Блог

У меня есть remarks таблица, которая может быть связана с любым количеством других элементов в системе, в случае этого примера мы будем использовать bookings , enquiries и referrals .

Таким образом, в remarks таблице у нас есть столбцы

 remark_id | datetime    | text | booking_id | enquiry_id | referral_id
1         | 2014-06-28  | abc  | 0          | 8          | 0
2         | 2014-06-27  | def  | 3          | 0          | 0
2         | 2014-05-31  | ghi  | 0          | 0          | 10
 

И т.д…

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

Я хотел бы достичь этого с помощью одного запроса, получив 2d-массив следующим образом:

 ['remark_id'=>1, 'datetime'=>'2014-06-28', 'text'=>'abc', 'name'=>'Harold']
 

Однако запрос, который я ожидал бы использовать, будет

  SELECT r.remark_id,r.datetime,r.text
 ,b.name AS book,rr.name AS referral,e.name AS enquiry
 FROM remarks AS r
 LEFT JOIN bookings AS b ON b.book_id=r.book_id
 LEFT JOIN referrals AS rr ON rr.referral_id=r.referral_id
 LEFT JOIN enquiries AS e ON e.enquiry_id=r.enquiry_id
 

Оставляя меня с выводом

 ['remark_id'=>1, 'datetime'=>'2014-06-28', 'text'=>'abc', 'book'=>'Harold', 'referral'=>'', 'enquiry'=>'']
 

И дополнительная обработка, которую нужно выполнить до или во время отображения его в представлении.

Есть ли способ написать запрос таким образом, чтобы он заполнял поле из первой NOT NULL строки, с которой он столкнулся в одной из объединенных таблиц?

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

Я могу придумать два способа:

1. используйте ОБЪЕДИНЕНИЕ:

    SELECT remark_id, datetime, text, name
   FROM remarks
   JOIN bookings ON (remarks.book_id = bookings.book_id)
   UNION 
   SELECT remark_id, datetime, text, name
   FROM remarks
   JOIN referrals ON (remarks.referral_id = referrals.referral_id)
   UNION
   SELECT remark_id, datetime, text, name
   FROM remarks
   JOIN enquiries ON (remarks.enquiry_id = enquiries.enquiry_id)</code>
    

2. используйте IFNULL (вероятно, намного медленнее):

    SELECT r.remark_id,r.datetime,r.text,
   IFNULL(b.name,IFNULL(rr.name,e.name)) AS name
   FROM remarks AS r
   LEFT JOIN bookings AS b ON b.book_id=r.book_id
   LEFT JOIN referrals AS rr ON rr.referral_id=r.referral_id
   LEFT JOIN enquiries AS e ON e.enquiry_id=r.enquiry_id</code>
    

Вариант 2 действительно намного медленнее из-за LEFT JOIN s.

Кроме того, обычно я бы не рекомендовал использовать 0 в качестве значения для несуществующих ссылок, а использовать NULL . Это позволит MySQL ускорить объединение.

один из способов добиться этого — использовать вложенные операторы if:

 if(b.name is not null, b.name, if(rr.name is not null, rr.name, e.name)) as name
 

одним из недостатков является то, что это дает неявный приоритет книгам? не уверен, что это будет проблемой.

возможно, главный недостаток, однако, заключается в том, что это своего рода «волшебство» и имеет тупой синтаксис, поэтому, в конце концов, было бы более понятно просто обрабатывать эти случаи в контроллере.

Кажется довольно запутанным, что у вас есть несколько неиспользуемых столбцов для каждой записи, если я не правильно понимаю. Если вы добавите больше таблиц, вам придется настроить каждое из представлений так, чтобы оно отфильтровывало новую таблицу.

У меня возникнет соблазн перепроектировать вашу структуру так, чтобы в каждой из таблиц был столбец remarkgroup_id, затем добавьте следующую таблицу примечаний

remark_id, remarkgroup_id, дата, сообщение

Это очистит лишние неиспользуемые столбцы и позволит вам использовать простую логику объединения.