Блог

Вот что я получил. Мы используем IlluminateDatabaseEloquentBuilder для построения материализованных представлений в Postgres

Три таблицы по сути, мы создаем список электронных писем, объединяя таблицу subscriptions и таблицу email, после чего мы получаем результат списка электронных писем подписчиков. Электронные письма.

На данный момент у нас есть третья таблица, в которой есть три элемента.

 Variable_ID | Email | Value
  

Ожидаемое поведение заключается в том, что пользователь выбирает переменную (например, адрес), а затем вводит значение, и мы сопоставляем значения на основе этого. Итак, затем мы берем эту объединенную таблицу подписок / электронных писем и сравниваем таблицу переменных / электронных писем / значений и находим совпадения.

Проблема заключалась в том, что я хочу это сделать:

$подписки->leftJoin(‘faker_email_var_table’, ‘faker_email_var_table’.’.email’, ‘=’, ’email.email’) ->выбрать(‘faker_email_var_table’.’.email’) ->где(‘variable_id’, ‘1002015’) ->где(‘значение’, ‘=’, ‘1234 Anywhere street’) -> где(‘variable_id’, ‘1002707’)-> где(‘значение’, ‘=’, ‘Смит’)

Это не работает. Потому что мне нужно сопоставить variable_id: 1002015 И значение: 1234 Anywhere street, а затем мне нужно сопоставить variable_id: 1002707 И значение: Smith

То, что работает в редакторе SQL, — это ПЕРЕСЕЧЕНИЕ.

 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
  

Но, когда я пытаюсь сделать что-то подобное:

 $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'));
  

Я получаю эту ошибку:
Call to undefined method Illuminate\Database\Eloquent\Builder::intersect()

Если бы это был php, я мог бы выполнить серию условных выражений IF, чтобы получить свои результаты, но в SQL этого нет.

Так что, если кто-нибудь может подсказать мне, как я могу создать INTERSECT без использования коллекции, это было бы здорово!

Спасибо, Стек Фам!

У меня это есть в одном из моих контроллеров, он использует EXCEPT , но вы можете изменить его на intersect.

Я предварительно создаю 2 отдельных запроса. Чем я:

 $query = Thread::query() 
    ->fromRaw( 
        '(SELECT * FROM ((' . $unioned->toSql() . ') EXCEPT ' . $excludeExplicit->toSql() . ') AS threads) AS threads', 
        array_merge($unioned->getBindings(), $excludeExplicit->getBindings()) 
    );
  

Мне было бы любопытно узнать, является ли это лучшим способом. По крайней мере, это помогает мне.

То, что я в итоге сделал, было довольно специфичным, и я хотел бы, чтобы был лучший способ. Но это работает.

Я упоминал, как это создает материализованное представление, большая часть которого создается с использованием Eloquent. В какой-то момент эта модель Eloquent builder преобразуется в raw SQL с использованием ->toSql() . Поэтому я просто объединяю INTERSECT ‘s после этого в toSql для каждой дополнительной переменной, добавленной в представление.

 $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;
}

  

Я создаю строку intersects с помощью цикла
Нашими переменными являются variable_id and variable_value .
Я упростил это, чтобы показать, что я сделал, конечно, для ясности.

Но я просто добавляю ПЕРЕСЕЧЕНИЯ после toSql() метода. Это некрасиво, и это не сработает, если вы не планируете создавать из него представление.

Но это то, что я сделал. И наоборот, я не смог найти другого способа получить совпадающие строки, которые я хотел, кроме или intersect. Что является своего рода облом.

Мне нужно было удалить строки из верхнего запроса и сопоставить и с несколькими ссылками из variable_relational_tbl

Например, мне нужны все строки с var_id = 123, где значение равно ‘1234 Example Street’, И все строки с var_id = 321, где значение равно ‘1234 Example Road’, Но поскольку строка не может иметь два разных пересечения var_id, это единственный способ, которым я мог это сделать.

Я не знаю, поможет ли это кому-нибудь, но у Eloquent нет INTERSECT . И это в основном ответ на этот вопрос.