ccdavies ccdavies - 5 months ago 20
MySQL Question

Multiple queries, or nested?

I have two channels.

The first channel is 'booking'. This holds the

member_id
and a
tour_id
.

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

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

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




exp_channel_data
|channel_id|entry_id|field_id_1|...|

exp_channel_titles
|channel_id|entry_id|author_id|...|

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

Assuming a table structure like this:

|channel_id|tour_id|member_id|field1|...|
-----------------------------------------
|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...

SELECT
    booking.tour_id
    booking.member_id
FROM
    my_table tours
    LEFT JOIN my_table booking
        ON tours.tour_id = booking.tour_id
        AND booking.channel_id = 'booking'
WHERE
    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:

SELECT
    titles.author_id
FROM
    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
WHERE
    tours.channel_id = 5
    AND tours.field_id_34 LIKE 'adas%'