Filter by tag=solo includes me& julio but filter by tag=solo and performer=Euge does not.

[speirling]

neither does tag=allmixedup

Also brown eyed girl - in tag=lively but not tag=livel + performer=euge

[speirling]

The following includes me & julio (taken from mysql general_log - see http://www.evernote.com/l/ANB4b-_0AopIyYGR1jL_0p1a-w_yS4dr4kc/ - when tag=lively but no performer set)

170612 14:28:33   141 Connect   root@localhost on music_admin
                  141 Query     SET time_zone = '+0:00'
                  141 Query     SET NAMES utf8
                  141 Query     SELECT `SongPerformances`.`id` AS `SongPerformances__id`, `SongPerformances`.`song_id` AS `SongPerformances__song_id`, `SongPerformances`.`timestamp` AS `SongPerformances__timestamp` FROM `song_performances` `SongPerformances` WHERE `SongPerformances`.`timestamp` BETWEEN "2017-06-12 10:28:33" AND "2017-06-12 13:28:33" GROUP BY `song_id`
                  141 Query     SELECT `SongVotes`.`id` AS `SongVotes__id`, `SongVotes`.`song_id` AS `SongVotes__song_id`, `SongVotes`.`timestamp` AS `SongVotes__timestamp` FROM `song_votes` `SongVotes` WHERE `SongVotes`.`timestamp` BETWEEN "2017-06-12 10:28:33" AND "2017-06-12 13:28:33" GROUP BY `song_id`
                  141 Query     SELECT `Performers`.`id` AS `Performers__id`, `Performers`.`nickname` AS `Performers__nickname` FROM `performers` `Performers`
                  141 Query     SELECT `Events`.`venue` AS `Events__venue` FROM `events` `Events` GROUP BY `venue`
                  141 Query     SELECT `Tags`.`id` AS `Tags__id`, `Tags`.`title` AS `Tags__title` FROM `tags` `Tags`
                  141 Query     SELECT `Songs`.`id` AS `Songs__id`, `Songs`.`title` AS `Songs__title`, `Songs`.`written_by` AS `Songs__written_by`, `Songs`.`performed_by` AS `Songs__performed_by`, `Songs`.`base_key` AS `Songs__base_key`, `Songs`.`content` AS `Songs__content` FROM `songs` `Songs` INNER JOIN `song_tags` `SongTags` ON `Songs`.`id` = (`SongTags`.`song_id`) INNER JOIN `tags` `Tags` ON (`Tags`.`id` in (13) AND `Tags`.`id` = (`SongTags`.`tag_id`)) GROUP BY `Songs`.`id`  HAVING COUNT(`Songs`.`id`) = '1'  ORDER BY `Songs`.`title` ASC
                  141 Query     SELECT `SongTags`.`id` AS `SongTags__id`, `SongTags`.`song_id` AS `SongTags__song_id`, `SongTags`.`tag_id` AS `SongTags__tag_id`, `Tags`.`id` AS `Tags__id`, `Tags`.`title` AS `Tags__title` FROM `song_tags` `SongTags` INNER JOIN `tags` `Tags` ON `Tags`.`id` = (`SongTags`.`tag_id`) WHERE `SongTags`.`song_id` in (31,34,50,56,58,59,66,82,85,86,883,98,105,867,107,110,111,117,121,123,126,128,3,132,135,137,915,139,141,142,951,153,4,647,18,176,182,909,187,198,737,212,675,751,218,908,226,249,953,256,290,927,296,309,735,956,312,321,876,945,741,342,344,361,366,648,740,761,17,814,672,938,442,443,447,794,462,738,491,914,22,494,506,508,511,685,518,522,525,534,543,552,553,560,574,892,588,925,592,789,885,617,620,29)
170612 14:28:34   141 Query     SELECT `SetSongs`.`id` AS `SetSongs__id`, `SetSongs`.`set_id` AS `SetSongs__set_id`, `SetSongs`.`song_id` AS `SetSongs__song_id`, `SetSongs`.`order` AS `SetSongs__order`, `SetSongs`.`performer_id` AS `SetSongs__performer_id`, `SetSongs`.`key` AS `SetSongs__key`, `SetSongs`.`capo` AS `SetSongs__capo`, `Performers`.`id` AS `Performers__id`, `Performers`.`name` AS `Performers__name`, `Performers`.`nickname` AS `Performers__nickname` FROM `set_songs` `SetSongs` INNER JOIN `performers` `Performers` ON `Performers`.`id` = (`SetSongs`.`performer_id`) WHERE `SetSongs`.`song_id` in (31,34,50,56,58,59,66,82,85,86,883,98,105,867,107,110,111,117,121,123,126,128,3,132,135,137,915,139,141,142,951,153,4,647,18,176,182,909,187,198,737,212,675,751,218,908,226,249,953,256,290,927,296,309,735,956,312,321,876,945,741,342,344,361,366,648,740,761,17,814,672,938,442,443,447,794,462,738,491,914,22,494,506,508,511,685,518,522,525,534,543,552,553,560,574,892,588,925,592,789,885,617,620,29)
170612 14:28:37   141 Quit

but the following does not (tag=lively, performer=euge)

170612 14:37:15   142 Connect   root@localhost on music_admin
                  142 Query     SET time_zone = '+0:00'
                  142 Query     SET NAMES utf8
                  142 Query     SHOW FULL COLUMNS FROM `songs`
                  142 Query     SHOW INDEXES FROM `songs`
                  142 Query     SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'music_admin' AND kcu.TABLE_NAME = 'songs' AND rc.TABLE_NAME = 'songs'
                  142 Query     SHOW TABLE STATUS WHERE Name = 'songs'
                  142 Query     SHOW FULL COLUMNS FROM `song_performances`
                  142 Query     SHOW INDEXES FROM `song_performances`
                  142 Query     SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'music_admin' AND kcu.TABLE_NAME = 'song_performances' AND rc.TABLE_NAME = 'song_performances'
                  142 Query     SHOW TABLE STATUS WHERE Name = 'song_performances'
                  142 Query     SELECT `SongPerformances`.`id` AS `SongPerformances__id`, `SongPerformances`.`song_id` AS `SongPerformances__song_id`, `SongPerformances`.`timestamp` AS `SongPerformances__timestamp` FROM `song_performances` `SongPerformances` WHERE `SongPerformances`.`timestamp` BETWEEN "2017-06-12 10:37:15" AND "2017-06-12 13:37:15" GROUP BY `song_id`
                  142 Query     SHOW FULL COLUMNS FROM `song_votes`
                  142 Query     SHOW INDEXES FROM `song_votes`
                  142 Query     SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'music_admin' AND kcu.TABLE_NAME = 'song_votes' AND rc.TABLE_NAME = 'song_votes'
                  142 Query     SHOW TABLE STATUS WHERE Name = 'song_votes'
                  142 Query     SELECT `SongVotes`.`id` AS `SongVotes__id`, `SongVotes`.`song_id` AS `SongVotes__song_id`, `SongVotes`.`timestamp` AS `SongVotes__timestamp` FROM `song_votes` `SongVotes` WHERE `SongVotes`.`timestamp` BETWEEN "2017-06-12 10:37:15" AND "2017-06-12 13:37:15" GROUP BY `song_id`
                  142 Query     SHOW FULL COLUMNS FROM `tags`
                  142 Query     SHOW INDEXES FROM `tags`
                  142 Query     SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'music_admin' AND kcu.TABLE_NAME = 'tags' AND rc.TABLE_NAME = 'tags'
                  142 Query     SHOW TABLE STATUS WHERE Name = 'tags'
                  142 Query     SHOW FULL COLUMNS FROM `performers`
                  142 Query     SHOW INDEXES FROM `performers`
                  142 Query     SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'music_admin' AND kcu.TABLE_NAME = 'performers' AND rc.TABLE_NAME = 'performers'
                  142 Query     SHOW TABLE STATUS WHERE Name = 'performers'
                  142 Query     SHOW FULL COLUMNS FROM `events`
                  142 Query     SHOW INDEXES FROM `events`
                  142 Query     SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'music_admin' AND kcu.TABLE_NAME = 'events' AND rc.TABLE_NAME = 'events'
                  142 Query     SHOW TABLE STATUS WHERE Name = 'events'
                  142 Query     SELECT `Performers`.`id` AS `Performers__id`, `Performers`.`nickname` AS `Performers__nickname` FROM `performers` `Performers`
                  142 Query     SELECT `Events`.`venue` AS `Events__venue` FROM `events` `Events` GROUP BY `venue`
                  142 Query     SELECT `Tags`.`id` AS `Tags__id`, `Tags`.`title` AS `Tags__title` FROM `tags` `Tags`
                  142 Query     SHOW FULL COLUMNS FROM `set_songs`
                  142 Query     SHOW INDEXES FROM `set_songs`
                  142 Query     SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'music_admin' AND kcu.TABLE_NAME = 'set_songs' AND rc.TABLE_NAME = 'set_songs'
                  142 Query     SHOW TABLE STATUS WHERE Name = 'set_songs'
                  142 Query     SHOW FULL COLUMNS FROM `song_tags`
                  142 Query     SHOW INDEXES FROM `song_tags`
                  142 Query     SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'music_admin' AND kcu.TABLE_NAME = 'song_tags' AND rc.TABLE_NAME = 'song_tags'
                  142 Query     SHOW TABLE STATUS WHERE Name = 'song_tags'
                  142 Query     SELECT `Songs`.`id` AS `Songs__id`, `Songs`.`title` AS `Songs__title`, `Songs`.`written_by` AS `Songs__written_by`, `Songs`.`performed_by` AS `Songs__performed_by`, `Songs`.`base_key` AS `Songs__base_key`, `Songs`.`content` AS `Songs__content` FROM `songs` `Songs` INNER JOIN `set_songs` `SetSongs` ON `Songs`.`id` = (`SetSongs`.`song_id`) INNER JOIN `performers` `Performers` ON (`Performers`.`id` = 1 AND `Performers`.`id` = (`SetSongs`.`performer_id`)) INNER JOIN `song_tags` `SongTags` ON `Songs`.`id` = (`SongTags`.`song_id`) INNER JOIN `tags` `Tags` ON (`Tags`.`id` in (13) AND `Tags`.`id` = (`SongTags`.`tag_id`)) GROUP BY `Songs`.`id`  HAVING COUNT(`Songs`.`id`) = '1'  ORDER BY `Songs`.`title` ASC
                  142 Query     SELECT `SongTags`.`id` AS `SongTags__id`, `SongTags`.`song_id` AS `SongTags__song_id`, `SongTags`.`tag_id` AS `SongTags__tag_id`, `Tags`.`id` AS `Tags__id`, `Tags`.`title` AS `Tags__title` FROM `song_tags` `SongTags` INNER JOIN `tags` `Tags` ON `Tags`.`id` = (`SongTags`.`tag_id`) WHERE `SongTags`.`song_id` in (31,56,58,66,883,123,3,132,137,915,141,153,18,176,737,212,751,249,953,256,290,296,735,312,876,342,366,938,442,443,447,794,462,491,22,494,506,508,685,525,543,553,574,925,789,885,29)
                  142 Query     SELECT `SetSongs`.`id` AS `SetSongs__id`, `SetSongs`.`set_id` AS `SetSongs__set_id`, `SetSongs`.`song_id` AS `SetSongs__song_id`, `SetSongs`.`order` AS `SetSongs__order`, `SetSongs`.`performer_id` AS `SetSongs__performer_id`, `SetSongs`.`key` AS `SetSongs__key`, `SetSongs`.`capo` AS `SetSongs__capo`, `Performers`.`id` AS `Performers__id`, `Performers`.`name` AS `Performers__name`, `Performers`.`nickname` AS `Performers__nickname` FROM `set_songs` `SetSongs` INNER JOIN `performers` `Performers` ON `Performers`.`id` = (`SetSongs`.`performer_id`) WHERE `SetSongs`.`song_id` in (31,56,58,66,883,123,3,132,137,915,141,153,18,176,737,212,751,249,953,256,290,296,735,312,876,342,366,938,442,443,447,794,462,491,22,494,506,508,685,525,543,553,574,925,789,885,29)
170612 14:37:17   142 Quit

[speirling]

[speirling]

The problem seems to be the difference in the lists of song ids in the last query:

(31,34,50,56,58,59,66,82,85,86,883,98,105,867,107,110,111,117,121,123,126,128,3,132,135,137,915,139,141,142,951,153,4,647,18,176,182,909,187,198,737,212,675,751,218,908,226,249,953,256,290,927,296,309,735,956,312,321,876,945,741,342,344,361,366,648,740,761,17,814,672,938,442,443,447,794,462,738,491,914,22,494,506,508,511,685,518,522,525,534,543,552,553,560,574,892,588,925,592,789,885,617,620,29) vs (31,56,58,66,883,123,3,132,137,915,141,153,18,176,737,212,751,249,953,256,290,296,735,312,876,342,366,938,442,443,447,794,462,491,22,494,506,508,685,525,543,553,574,925,789,885,29)

[speirling]

That list seems to match the output of the earlier queries SELECTSongs.idASSongsid,Songs.titleASSongstitle,Songs.written_byASSongswritten_by,Songs.performed_byASSongsperformed_by,Songs.base_keyASSongsbase_key,Songs.contentASSongs__contentFROMsongs`Songs INNER JOIN song_tags SongTags ON Songs.id = (SongTags.song_id) INNER JOIN tags Tags ON (Tags.id in ('13') AND Tags.id = (SongTags.tag_id)) GROUP BY Songs.id HAVING COUNT(Songs.id) = '1' ORDER BY Songs.title ASC vs SELECT Songs.id AS `Songsid,Songs.titleASSongstitle,Songs.written_byASSongswritten_by,Songs.performed_byASSongsperformed_by,Songs.base_keyASSongs__base_key,Songs.contentASSongscontentFROMsongs`Songs INNER JOIN set_songs SetSongs ON Songs.id = (SetSongs.song_id) INNER JOIN performers Performers ON (Performers.id = '1/ SongListComponent Q8 : limit to specific performer /' AND Performers.id = (SetSongs.performer_id)) INNER JOIN song_tags SongTags ON Songs.id = (SongTags.song_id) INNER JOIN tags Tags ON (Tags.id in ('13') AND Tags.id = (SongTags.tag_id)) GROUP BY Songs.id HAVING COUNT(Songs.id) = '1' ORDER BY Songs.title ASC`

[speirling]

The difference is that the second query has INNER JOIN set_songs SetSongs ON Songs.id = (SetSongs.song_id) INNER JOIN performers Performers ON (Performers.id = '1' AND Performers.id = (SetSongs.performer_id))

[speirling]

The line INNER JOIN set_songs SetSongs ON Songs.id = (SetSongs.song_id) Seems to be the one that makes the key difference.

But why? set_songs includes all of the missing song_ids!

[speirling]

SELECT * FROM songs Songs INNER JOIN set_songs SetSongs ON Songs.id = (SetSongs.song_id)

Seems to include the required songs. So it's an interraction between set_songs and one of the later tables - song_tags or tags

[speirling]

This includes all of the required songs - it's the full query, but not grouped:

SELECT * FROM songs Songs INNER JOIN set_songs SetSongs ON Songs.id = (SetSongs.song_id) INNER JOIN performers Performers ON (Performers.id = '1' AND Performers.id = (SetSongs.performer_id)) INNER JOIN song_tags SongTags ON Songs.id = (SongTags.song_id) INNER JOIN tags Tags ON (Tags.id in ('13') AND Tags.id = (SongTags.tag_id))

[speirling]

Thi seems to give the required result:

SELECT * FROM songs Songs INNER JOIN set_songs SetSongs ON Songs.id = (SetSongs.song_id) INNER JOIN performers Performers ON (Performers.id = '1' AND Performers.id = (SetSongs.performer_id)) INNER JOIN song_tags SongTags ON Songs.id = (SongTags.song_id) INNER JOIN tags Tags ON (Tags.id in ('13') AND Tags.id = (SongTags.tag_id)) GROUP BY Songs.id

[speirling]

but if you add

HAVING COUNT(Songs.id) = '1'

you lose some of them - 34, 59,86,105,107,142,187,226,344,361,511,518,543,675,761,867,956

[speirling]

Interesting. The ones that are missing are all of the ones that have multiple entries before the GROUP BY clause is added. The HAVING COUNT() = 1 excludes ones that have multiples. I suppose that makes sense. But how are they multiple ?

[speirling]

They become multiple when

INNER JOIN set_songs SetSongs ON Songs.id = (SetSongs.song_id) is added

[speirling]

But song 22 is also multiple in that table, but not in the later query?

[speirling]

22 becomes no longer multiple when INNER JOIN performers Performers ON (Performers.id = '1' AND Performers.id = (SetSongs.performer_id)) is added.

So it's rare that a song is multiple in the overall query. Is it a fault that songs are multiple in the overall query, or should the HAVIng clause be dropped?

[speirling]

The $filtered_list_query->having(['COUNT(Songs.id) = ' => sizeof($selected_tag_array)]); line is in the tag section of the query and is required to ensure that only songs that are associated with all of the specified tags are listed.

[speirling]

Simpler test - 3 tags - this should only have 4 songs in the result:

SELECT `Songs`.`id` AS `Songs__id`, `Songs`.`title` AS `Songs__title`, SongTags.*, Tags.*
FROM `songs` `Songs` 
      INNER JOIN `song_tags` `SongTags` ON `Songs`.`id` = (`SongTags`.`song_id`) 
      INNER JOIN `tags` `Tags` ON (`Tags`.`id` in (13,15,44) AND `Tags`.`id` = (`SongTags`.`tag_id`)) 
GROUP BY `Songs`.`id`  
HAVING COUNT(`Songs`.`id`) = '3'
ORDER BY `Songs`.`title` ASC

result:

Songs__id | Songs__title      | id     | song_id | tag_id
883          | Baker Street     | 186  | 883       | 13
867          | Budapest         | 112  | 867       | 13
675          | Half Way          | 95    | 675       | 13
344          | Me And Julio    | 128  | 344       | 15

Adding in the Performer messes this up completely:

SELECT `Songs`.`id` AS `Songs__id`, `Songs`.`title` AS `Songs__title`, SongTags.*, Tags.*, SetSongs.id, Performers.*
FROM `songs` `Songs` 
           INNER JOIN `song_tags` `SongTags` ON `Songs`.`id` = (`SongTags`.`song_id`) 
           INNER JOIN `tags` `Tags` ON (`Tags`.`id` in (13,15,44) AND `Tags`.`id` = (`SongTags`.`tag_id`)) 
           INNER JOIN `set_songs` `SetSongs` ON `Songs`.`id` = (`SetSongs`.`song_id`) 
           INNER JOIN `performers` `Performers` ON (`Performers`.`id` = 1 AND `Performers`.`id` = (`SetSongs`.`performer_id`)) 
GROUP BY `Songs`.`id`  
HAVING COUNT(`Songs`.`id`) = '3' 
ORDER BY `Songs`.`title` ASC`

5 songs in the result, andthose don't include all of the tags

Songs__id | Songs__title           | id       | song_id | tag_id  | id  | title             | id    | id | name          | nickname
59            | All Shook Up         | 325    | 59         | 13       | 13 | Lively (Fast) | 165 | 1 | Eugene Peelo | Euge
883          | Baker Street          | 186    | 883       | 13        | 13 | Lively (Fast) | 517 | 1 | Eugene Peelo | Euge
682          | Only You               | 414    | 682       | 15       | 15 | AllMixedUp | 169 | 1 | Eugene Peelo | Euge
932          | Rat Trap                 | 1257 | 932       | 15        | 15 | AllMixedUp | 549 | 1 | Eugene Peelo | Euge
628          | Wonderful Tonight | 419   | 628       | 15         | 15 | AllMixedUp | 162 | 1 | Eugene Peelo | Euge

[speirling]

When joined with Set_songs (so that performer can be determined) the number of entries is multiplied. If A Hard Day's night is in 2 sets, then where there are 2 entries matching 2 of the 3 specified tags, there will now be 4 entries, 2 for each tag in each set. The HAVING COUNT won't work. In fact it's unlikely to work at all, it's more surprising that it works for so many of the original use case, and that's probably because there's only 1 tag and many of the songs are only used in the 'base' set i.e. only used in 1 set so the COUNT matches for many of them.

[speirling]

How can I use COUNT to only count the tags, when joined to a 3rd table?

[speirling]

Clearer Example of the problem:

SELECT 
    `Songs`.`id` AS `Songs__id`, 
    `Songs`.`title` AS `Songs__title`,  
    Tags.id AS Tag_id, 
    Tags.title AS Tag, 
    SetSongs.id AS Set_song_id, 
    SetSongs.set_id AS Set_ID, 
    Performers.id AS Performer_id, 
    Performers.nickname AS Performer
FROM `songs` `Songs` 
    INNER JOIN `song_tags` `SongTags` ON `Songs`.`id` = (`SongTags`.`song_id`) 
    INNER JOIN `tags` `Tags` 
        ON (`Tags`.`id` in (13,15,44) 
        AND `Tags`.`id` = (`SongTags`.`tag_id`))  
    INNER JOIN `set_songs` `SetSongs` 
        ON `Songs`.`id` = (`SetSongs`.`song_id`) 
    INNER JOIN `performers` `Performers` 
        ON (`Performers`.`id` = 1 
        AND `Performers`.`id` = (`SetSongs`.`performer_id`)) 
ORDER BY `Songs`.`id` ASC
LIMIT 1000

[speirling]

See:

Songs__id	Songs__title	Tag_id	Tag	Set_song_id	Set_ID	Performer_id	Performer
344	Me And Julio Down By The Schoolyard	15	AllMixedUp	453	44	1	Euge
344	Me And Julio Down By The Schoolyard	44	Solo	453	44	1	Euge
344	Me And Julio Down By The Schoolyard	13	Lively (Fast)	223	11	1	Euge
344	Me And Julio Down By The Schoolyard	15	AllMixedUp	223	11	1	Euge
344	Me And Julio Down By The Schoolyard	44	Solo	223	11	1	Euge
344	Me And Julio Down By The Schoolyard	13	Lively (Fast)	453	44	1	Euge

2 sets include that song, and it is associated with all 3 tags. That's 6 entries. But each song might be in different combinations of sets.

[speirling]

Can you group / unique combinations like song + tag? Or join the result of a subquery to other tables? - i.e. run the tag query and then join the result???

[speirling]

This seems to give the right result for the 3-tag query:

SELECT 
    `Songs`.`id` AS `Songs__id`, 
    `Songs`.`title` AS `Songs__title`/*, 
    `Songs`.`written_by` AS `Songs__written_by`, 
    `Songs`.`performed_by` AS `Songs__performed_by`, 
    `Songs`.`base_key` AS `Songs__base_key`, 
    `Songs`.`content` AS `Songs__content`*/,
subquery_SongWithAllTags.* 
FROM `songs` `Songs` 

INNER JOIN `set_songs` `SetSongs` ON `Songs`.`id` = (`SetSongs`.`song_id`) 
INNER JOIN `performers` `Performers` ON (`Performers`.`id` = '1' AND `Performers`.`id` = (`SetSongs`.`performer_id`)) 

INNER JOIN (
    SELECT `Songs_sub`.`id` AS `Songs_sub_id`, `Songs_sub`.`title` AS `Songs_sub_title`, Tags.id AS Tag_Id, Tags.title AS Tags__title
    FROM `songs` `Songs_sub` 
          INNER JOIN `song_tags` `SongTags` ON `Songs_sub`.`id` = (`SongTags`.`song_id`) 
          INNER JOIN `tags` `Tags` ON (`Tags`.`id` in (13,15,44) AND `Tags`.`id` = (`SongTags`.`tag_id`)) 
    GROUP BY `Songs_sub`.`id`  
    HAVING COUNT(`Songs_sub`.`id`) = '3'
) subquery_SongWithAllTags ON `Songs`.`id` = Songs_sub_id
GROUP BY `Songs`.`id`
ORDER BY `Songs`.`title` ASC

[speirling]

That approach seems to geive the correct result for the initial use case also.

Now how do you do that in CakePHP3?

[speirling]

It took a couple of hours to figure out the syntax, but this seems to be working:

@@ -95,6 +95,12 @@ class songlistComponent extends Component {
                        }

                        // Tags: Limit the result to songs that are associated with any of the passed array of tags
+                       /*
+                        * This has to be done as a subquery, because a HAVING COUNT() must be used to ensure that only songs that are associated with _all_ of the specified tags will be displayed.
+                        * That statement interferes with larger more complex queries - e.g. filtered byt Tag _and_ Performer, and can filter out any records that have multiple entries in the final qu
+                        *                 $filtered_list_query->having(['COUNT(Songs.id) = ' => sizeof($selected_tag_array)]);
+                        * Keeping the HAVING COUNT() inside a subquery seems to avoid that problem
+                        */
                        if (
                                        array_key_exists('filter_tag_id', $controller->request->data)
                                        && $controller->request->data['filter_tag_id']
@@ -102,20 +108,21 @@ class songlistComponent extends Component {
                                ) {
                                $filter_on = true;
                                $selected_tag_array = $controller->request->data['filter_tag_id'];
-                               $filtered_list_query->matching(
+                               $subquery_SongWithAllTags = $controller->Songs->find();
+                               $subquery_SongWithAllTags->matching(
                                        'SongTags.Tags', function ($q) use ($selected_tag_array)  {
                                        $q->where(['Tags.id IN' => $selected_tag_array]);
                                        return $q;
                                        }
                                );
-                               $filtered_list_query->group('Songs.id');
-                               /*
-                                * The following line was added to ensure that only songs that are associated with _all_ of the specified tags will be dsplayed.
-                                * Unfortunately that interferes with larger more complex queries - e.g. filtered byt Tag _and_ Performer,
-                                * And in that case can filter out any records that have multiple entries in the final query.
-                                *                 $filtered_list_query->having(['COUNT(Songs.id) = ' => sizeof($selected_tag_array)]);
-                                */
-                               $filtered_list_query->having(['COUNT(Songs.id) = ' => sizeof($selected_tag_array)]);
+                               $subquery_SongWithAllTags->group('Songs.id');
+                               $subquery_SongWithAllTags->having(['COUNT(Songs.id) = ' => sizeof($selected_tag_array)]);
+                               $filtered_list_query->Join([
+                                       'table' => $subquery_SongWithAllTags,
+                                       'alias' => 'subquery_SongWithAllTags',
+                                       'type' => 'INNER',
+                                       'conditions' => '`subquery_SongWithAllTags`.`Songs__id` = `Songs`.`id`'
+                               ]);
                        } else {
                                $selected_tag_array = [];
                        }