如何在没有收藏的情况下使用Eloquent Builder进行INTERSECT [英] How do I make an INTERSECT using Eloquent Builder without having a collection

问题描述

这就是我得到的.我们使用 Illuminate \ Database \ Eloquent \ Builder 在Postgres中构建物化视图

Here's what I got. We use Illuminate\Database\Eloquent\Builder to build out Materialized Views in Postgres

本质上是三个表，我们通过连接订阅表和电子邮件表来构建电子邮件列表，然后得到订阅者电子邮件的电子邮件列表.

Three tables essentially, We build the list of emails by joining subscriptions table and an emails table, then we have a resulting of list of emails of subscribers emails.

这时，我们有了第三个表，其中包含三项内容.

At this point, we have a third table, which has three things in it.

Variable_ID | Email | Value

预期的行为是用户选择一个变量(例如地址)，然后输入一个值，然后我们根据该值匹配值.因此，我们采用该订阅/电子邮件联接表，并比较变量/电子邮件/值表并找到匹配项.

The expected behavior is that a user picks a variable, (e.g. Address) and then enters a value, and we match values based on that. So then we take that subscriptions/emails joined table, and compare the variables/emails/values table and find matches.

问题在于我想这样做:

$ subscriptions-> leftJoin('faker_email_var_table'，'faker_email_var_table'.'.email'，'='，'emails.email')->选择('faker_email_var_table'.'.email')-> where('variable_id'，'1002015')-> where('value'，'='，'1234 Anywhere street')-> where('variable_id'，'1002707')-> where('value'，'='，'Smith')

$subscriptions->leftJoin('faker_email_var_table', 'faker_email_var_table'.'.email', '=', 'emails.email') ->select('faker_email_var_table'.'.email') ->where('variable_id', '1002015')->where('value', '=', '1234 Anywhere street') ->where('variable_id', '1002707')->where('value', '=', 'Smith')

那是行不通的.因为我需要匹配variable_id:1002015 AND值:1234在任何地方的街道然后我需要匹配variable_id:1002707 AND值:Smith

That doesn't work. Because I need match the variable_id: 1002015 AND value: 1234 Anywhere street And then I need to match variable_id: 1002707 AND value: Smith

在SQL编辑器中有效的是一个INTERSECT.

What works in SQL Editor is an INTERSECT.

SELECT var_Email_Val_Table.email
   FROM backend.subscriptions
     JOIN backend.emails ON subscriptions.email_id = emails.id
     LEFT JOIN backend.var_Email_Val_Table ON var_Email_Val_Table.email::text = emails.email
  WHERE var_Email_Val_Table.variable_id = 1002015 AND var_Email_Val_Table.value::text = '1234 Anywhere street'::text

INTERSECT

SELECT var_Email_Val_Table.email from backend.var_Email_Val_Table 
  WHERE var_Email_Val_Table.variable_id = 1002707 AND 
var_Email_Val_Table.value::text = 'Rhodes'::text

INTERSECT

 SELECT var_Email_Val_Table.email from backend.var_Email_Val_Table 
  WHERE var_Email_Val_Table.variable_id = 1002706 AND var_Email_Val_Table.value::text = 'Engineer'::text

但是，当我尝试这样的事情时:

But, when I try something like this:

$subscriptions->leftJoin(var_Email_Val_Table, var_Email_Val_Table.'.email', '=', 'emails.email')
    ->select(var_Email_Val_Table.'.email')
    ->where('variable_id', '1002015')->where('value', '=', '1234 Anywhere street')
    ->intersect(DB::table(var_Email_Val_Table)->select(var_Email_Val_Table.'.email')
    ->where('variable_id', '1002707')->where('value', '=', 'Smith'));

我收到此错误:调用未定义的方法Illuminate \\ Database \\ Eloquent \\ Builder :: intersect()

如果这是php，我可以执行一系列IF条件获取结果，但这在SQL中不重要.

If this was php, I could do a series of IF conditionals to get my results, but that's not a thing in SQL.

因此，如果有人可以在不使用集合的情况下帮助我如何雄辩地进行交集，那就太好了！

So if any one can lend me a hand on how I can Eloquent an INTERSECT without using a collection, that would be great!

感谢Stack Fam！

Thanks Stack Fam!

推荐答案

我最后做的事情很具体，我希望有更好的方法.但这有效.

What I wound up doing was rather specific, and I wish there was a better way. But it works.

我提到了它如何构建物化视图，其中大部分是使用Eloquent构建的.在某个时候，这个雄辩的构建器模型使用-> toSql()转换为原始SQL.因此，我只是在ToSql之后将 INTERSECT 的连接起来，添加到视图中的每个其他变量.

I mentioned how this builds a Materialized View, most of which is built using Eloquent. At some point, this Eloquent builder model converts to raw SQL using ->toSql(). So I just concatenate the INTERSECT's on after that ToSql for each additional variable added to the view.

$rawQuery = $subscriptions->leftJoin('variable_relational_tbl', 'variable_relational_tbl'.'email', '=', 'emails.email')
            ->select('variable_relational_tbl'.'.email')
            ->join('emails', 'subscriptions.email_id', 'emails.id')
            ->select('emails.email', 'emails.md5 as email_md5')->toSql();

$intersect = $this->buildIntersects($query['variables']);

$query = $this->formatQuery($subscriptions);
if (! empty($intersect)) {
    $query = $query.' '.$intersect;
}

// INTERSECT QUERY BUILDER
protected function buildIntersects($variables)
{
    $interstr = ' INTERSECT ';
    $subquery = '';
    $cntr = 0;
    foreach ($variables as $var) {
        if ($cntr > 0) {
            $subquery = $interstr." SELECT `variable_relational_tbl`.email FROM`variable_relational_tbl` 
                WHERE `variable_relational_tbl`.variable_id = '$var['variable_id']' AND `variable_relational_tbl`.value = '$var['variable_value']' ";
        }
    }
    return $subquery;
}

我用循环构建了相交字符串我们的变量是 variable_id 和 variable_value .为了简化起见，我对此进行了简化.

I build out the intersects string, with a loop Our variables are variable_id and variable_value. I've simplified this to show what I did of course for clarity.

但是我只是在 toSql()方法之后添加了INTERSECTS.它不是很漂亮，除非您计划从中进行查看，否则它将无法正常工作.

But I just tack on the INTERSECTS after the toSql() method. It isn't pretty, and it wouldn't work unless you are planning on making a view out of it.

但这就是我所做的.相反，我找不到另一种方法来获得我想要的匹配行，除了或相交.真是个无赖.

But that's what I did. Conversely, I could not find another way to get the matching rows I wanted except or intersect. Which is kind of a bummer.

我需要从顶部查询中删除行，并从 variable_relational_tbl

I needed to remove rows from the top query and match and with Multiple cols from the variable_relational_tbl

就像我需要var_id = 123的所有行，其中值是'1234 Example Street'AND var_id = 321的所有行，其中值为'1234 Example Road'但是由于一行不能有两个不同的var_id的INTERSECTS，所以我可以做到这一点.

Like I need all rows with var_id = 123 where value is '1234 Example Street' AND all rows with var_id = 321 where value is '1234 Example Road' But since a row cannot have two different var_id's INTERSECTS is the only way I could do this.

我不知道这是否会帮助任何人，但是Eloquent没有 INTERSECT .这基本上就是这个问题的答案.

I don't know if this will ever help anyone, but Eloquent does NOT have an INTERSECT. And that's basically the answer to this question.

这篇关于如何在没有收藏的情况下使用Eloquent Builder进行INTERSECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！