For the data experts: Query over 2 MtoM relations, doable without direct SQL?


#1

Hey,

this should be nice for those who like puzzles :slight_smile:

I have a model in which I want to have 2 additional fields which come from tables linked by ManyToMany relationships:

So I need the tour model, and in addition:

  • from table group the sum of all amount_guests for each tour
  • from table guide the collected nicknames for each tour

After lots of trying, I found the correct SQL:

SELECT
    `id`,
    `name`,
    `start_date`,
    `start_time`,
    `max_guests`,
    (
    SELECT
        COALESCE(SUM(`amount_guests`),
        0)
    FROM
        `group` `G_g`
    WHERE
        `id` in (SELECT `group_id` FROM `group_to_tour` WHERE `group_to_tour`.`tour_id` = `tour`.`id`)
	) `sum_amount_guests`,
	(
    SELECT
        GROUP_CONCAT(`nickname`)
    FROM
        `guide`
    WHERE
        `id` in (SELECT `guide_id` FROM `guide_to_tour` WHERE `guide_to_tour`.`tour_id` = `tour`.`id`)
	) `nicknames`
FROM
    `tour`
WHERE
    `start_date` >= '2018-01-01' AND `start_date` < '2019-01-01'
ORDER BY
    `start_date`,
    `start_time`

This is what the result looks like:

Ok, now I want to try to create this directly in ATK without writing SQL. What I am lost on are the subselects over 2 tables. I tried something which comes very close:

$tour_model = $lcl->setModel(new Pmg\Tour($app->db), ['id', 'name', 'start_date', 'start_time']);
$sum = $tour_model->ref('GroupToTour')->ref('group_id')->action('fx0', ['sum', 'amount_guests']);
$tour_model->addExpression('sum_amount_guests', $sum);

$nicknames = $tour_model->ref('GuideToTour')->ref('guide_id')->action('fx0', ['group_concat', 'nickname']);
$tour_model->addExpression('nicknames', $nicknames);

This produces the following query:

SELECT
    `id`,
    `name`,
    `tour_type_id`,
    `meeting_point_id`,
    `start_date`,
    `start_time`,
    `end_date`,
    `end_time`,
    `max_guests`,
    `bookable_online`,
    `status`,
    `tourlist_printed`,
    `link_to_photos`,
    `details`,
    (
    SELECT
        COALESCE(SUM(`amount_guests`),
        0)
    FROM
        `group` `G_g`
    WHERE
        `id` IN(
        SELECT
            `group_id`
        FROM
            `group_to_tour` `G`
        WHERE
            `tour_id` IN(
            SELECT
                `id`
            FROM
                `tour`
            ORDER BY
                `start_date`,
                `start_time`
        )
    )
) `sum_amount_guests`,
(
    SELECT
        COALESCE(GROUP_CONCAT(`nickname`),
        0)
    FROM
        `guide` `G_g`
    WHERE
        `id` IN(
        SELECT
            `guide_id`
        FROM
            `guide_to_tour` `G`
        WHERE
            `tour_id` IN(
            SELECT
                `id`
            FROM
                `tour`
            ORDER BY
                `start_date`,
                `start_time`
        )
    )
) `nicknames`
FROM
    `tour`
WHERE
    `start_date` >= '2018-01-01' AND `start_date` < '2019-01-01'
ORDER BY
    `start_date`,
    `start_time`

The only fault is that the subquerys use all Tour ids, not just the current one. as a result, the aggregates return the total aggregate for ALL tours:

Any recommendations how to do the very last step?

p.s.: Really nice to build such a query with just 5 lines of code!
Best Regards
Philipp


#2

@DarkSide, looks like your kind of puzzle :smiley: :smiley: :smiley:


#3

Just wanted to give feedback on this:
As I found the correct SQL, I simply created a View in MySQL using this code.
In ATK, I created a simple Model using this table.

Best regards
Philipp