ccdavies ccdavies - 1 year ago 58
MySQL Question

Multiple queries, or nested?

I have two channels.

The first channel is 'booking'. This holds the

and a

The second channel is 'tours'. This holds the tour details, referenced by the above

I need to query fields within the 'tours' channel, getting the
for those where the query was met. I then need to find all 'booking' entries where the
matches one of those were the query was true.

Is it better to complete two SQL queries, or a nested query? If nested, how?



SELECT titles.author_id
FROM exp_channel_data AS tours
LEFT JOIN exp_channel_titles AS titles ON bookings.entry_id = titles.entry_id
LEFT JOIN exp_channel_data AS bookings
ON tours.entry_id = bookings.entry_id
AND bookings.channel_id = '17'
WHERE tours.channel_id = '5'
AND tours.field_id_34 = 'DSV04'

$sql_1 = "
SELECT entry_id
FROM exp_channel_data
WHERE field_id_34 LIKE 'adas%'

$query_1 = ee()->db->query($sql_1);
$test = $query_1->result_array();

$uids = Array();
foreach($test as $u) $uids[] = $u['entry_id'];
$list = implode(",",$uids);

$sql_2 = "
SELECT ct.author_id
FROM exp_channel_data AS cd
LEFT JOIN exp_channel_titles AS ct ON cd.entry_id = ct.entry_id
WHERE field_id_227 IN ($list)

$query_2 = ee()->db->query($sql_2);
$test_2 = $query_2->result_array();

Answer Source

Assuming a table structure like this:

|tours     |1      |NULL     |abc   |   |
|tours     |2      |NULL     |def   |   |
|booking   |1      |1        |      |   |
|booking   |1      |2        |      |   |
|booking   |2      |3        |      |   |

You could join the table on itself to get the results you are looking for...

    my_table tours
    LEFT JOIN my_table booking
        ON tours.tour_id = booking.tour_id
        AND booking.channel_id = 'booking'
    tours.channel_id = 'tours'
    AND tours.field1 = 'abc'

EDIT as follow-up to the updated question

I understand that:

  • you want to find the authors of all bookings for all tours where field_id_34 begins with "adas".
  • authors of bookings are maintained in the table exp_channel_titles
  • bookings and tours are maintained in the table exp_channel_data and the column channel_id is used to differentiate between bookings (17) and tours (5).
  • bookings and tours are linked by the entry_id of a tour and the field_id_227 of a booking
  • bookings and authors are linked by the booking's entry_id and the entry_id in exp_channel_titles

So this should give you the list of authors in one query:

    exp_channel_data tours
    LEFT JOIN exp_channel_data bookings
        ON bookings.channel_id = 17
        AND bookings.field_id_227 = tours.entry_id
    LEFT JOIN exp_channel_titles titles
        ON titles.entry_id = bookings.entry_id
    tours.channel_id = 5
    AND tours.field_id_34 LIKE 'adas%'