How to find a records with two or more relationships?

[ahiipsa]

How to find a records with two or more relationships using SequelizeJS?

For example:
Find photos with exactly two tags:

SELECT photo.*,
    FROM photo 
    LEFT JOIN photo_tag_relation ON photo_tag_relation.photo_id = photo.id
    LEFT JOIN tag ON tag.id = photo_tag_relation.tag_id
 WHERE tag.name IN ('tefal', 'iron')
 GROUP BY photo_tag_relation.photo_id
 HAVING COUNT(photo_tag_relation.photo_id) >= 2;

I tried to create a subquery:

db.models.Tag
    .findAll( {
        attributes: ['photo_tag_relation.photo_id'],
        where: {name: ['tefal', 'iron']},
        include: [db.models.PhotoTagRelation],
        group: ['photo_tag_relation.photo_id'],
        having: ['COUNT(?) >= ?', '`photo_id`', 2]
    })

However the resulting query contains other columns besides 'photo_id':

SELECT `photo_tag_relations`.`photo_id`,
       `photo_tag_relations`.`tag_id`       AS
       `photo_tag_relations.tag_id`,
       `photo_tag_relations`.`photo_id` AS
       `photo_tag_relations.vobject_uuid`
FROM   `tag`
       LEFT OUTER JOIN `photo_tag_relation` AS `photo_tag_relations`
                    ON `tag`.`id` = `photo_tag_relations`.`tag_id`
WHERE  `tag`.`name` IN ( 'tefal', 'iron' )
GROUP  BY `photo_tag_relations`.`photo_id`
HAVING Count('`photo_id`') >= 2;

[mickhansen]

Other columns? Well yes obviously, you're including a relation :) You can use attributes on the include statement aswell.

You might want to try:

having: ['COUNT(?) >= ?', '`photo_tag_relation`.`photo_id`', 2]

[ahiipsa]

I did it with two queries:

db.models.PhotoTagRelation
    .findAll({
        include: [
            {model: db.models.Tag, where: {name: ['tefal', 'iron']}}
        ],
        group: ['photo_id'],
        having: ['COUNT(?) >= ?', '`photo_id`', 2]
    })
    .success(function(result){

        db.models.Photo
            .findAndCountAll({
                where: {id: result.map( function(item){ return item.photo_id }  )}
            })
            .success(function( result ){


            })

    })

But i want to do it in one query, can i get assemble query?

db.models.PhotoTagRelation.findAll().getAssembleSqlQuery()?

or put subquery to where option like this:

db.models.Photo
            .findAndCountAll({
                where: {id: db.models.PhotoTagRelation.findAll() } // :)
            })
            .success(function( result ){

            })

[mickhansen]

No it's not possible to combine find calls like that. Did you try what i suggested?
I don't use having much but i imagine it's possible to do in one query, with sequelize too

[ahiipsa]

No it's not possible to combine find calls like that. Did you try what i suggested?

No, i didn't. I do not understand what you mean, because your code is similar to mine. Can write a complete example, please?

[mickhansen]

Similar but different, i targeted a more specific field. Well try out my change first and show me how the SQL looks like and what error it logs, i don't have any experience with HAVING so i can't write up a full example.

[ahiipsa]

Query is valid ( my query also )

SELECT `photo_tag_relations`.`photo_id`,
       `photo_tag_relations`.`tag_id`       AS
       `photo_tag_relations.tag_id`,
       `photo_tag_relations`.`photo_id` AS
       `photo_tag_relations.vobject_uuid`
FROM   `tag`
       LEFT OUTER JOIN `photo_tag_relation` AS `photo_tag_relations`
                    ON `tag`.`id` = `photo_tag_relations`.`tag_id`
WHERE  `tag`.`name` IN ( 'tefal', 'iron' )
GROUP  BY `photo_tag_relations`.`photo_id`
HAVING Count('`photo_tag_relation`.`photo_id`') >= 2;

I find solution...

[mickhansen]

Great, it works? :)

[ahiipsa]

I find solution how do it in one query.

I have the following JS code:

db.models
    .Vobject
    .findAndCountAll({
    where: [{type: 1 }],
    include: [{model: db.models.Tag, where: {name: ['iron', 'tefal']}}],
    limit: 10
})

The problem is that sequelize generates the following query:

select object where type = 2 limit 10 after find tags iron or tefal for this object

For example:

SELECT attributes
FROM   (SELECT `vobject`.*
        FROM   `vobject`
        WHERE  ( `vobject`.`type` = 1 )
        LIMIT  10) AS `vobject`
       INNER JOIN `vobject_tag_to_vobject` AS
                  `vobject_tags.vobject_tag_to_vobject`
               ON `vobject`.`uuid` =
       `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
       INNER JOIN `vobject_tag` AS `vobject_tags`
               ON `vobject_tags`.`id` =
`vobject_tags.vobject_tag_to_vobject`.`tag_id`
                  AND `vobject_tags`.`name` IN ( 'iron', 'tefal' );

But I need the generated SQL query to look like this:

select object where type = 2 and tags in (iron,tefal) limit 10

For example:

SELECT attributes
FROM   `vobject`
       INNER JOIN `vobject_tag_to_vobject` AS
                  `vobject_tags.vobject_tag_to_vobject`
               ON `vobject`.`uuid` =
       `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
       INNER JOIN `vobject_tag` AS `vobject_tags`
               ON `vobject_tags`.`id` =
`vobject_tags.vobject_tag_to_vobject`.`tag_id`
                  AND `vobject_tags`.`name` IN ( 'iron', 'tefal' )
WHERE  ( `vobject`.`type` = 1 )
LIMIT  10;

This happens because of the subquery requirement check in QueryGenerator.selectQuery

, subQuery = limit && (options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation )

After, I created the option.filterByInclude and added it to the condition, everything started to work fine :)

, subQuery = limit && !option.filterByInclude (options.hasIncludeWhere || options.hasIncludeRequired || options.hasMultiAssociation )

Now it returns objects with either 'iron' or 'tefal' tag.

The next step is to find object with both 'iron' and 'tefal' tags. I modified my query as follows:

• added group by object uuid clause
• added having where objects >= 2 clause

db.models
    .Vobject
    .findAndCountAll({
    where: [{type: 1 }],
        include: [{model: db.models.Tag, where: {name: ['iron', 'tefal']}}],
        limit: 10,
        filterByInclude: true,
        group: ['`vobject`.`uuid`'],
        having: ['COUNT(?) >= ?', '`vobject`.`uuid`', 2]
})

SELECT attributes...
FROM   `vobject`
       INNER JOIN `vobject_tag_to_vobject` AS
                  `vobject_tags.vobject_tag_to_vobject`
               ON `vobject`.`uuid` =
       `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
       INNER JOIN `vobject_tag` AS `vobject_tags`
               ON `vobject_tags`.`id` =
`vobject_tags.vobject_tag_to_vobject`.`tag_id`
                  AND `vobject_tags`.`name` IN ( 'iron', 'tefal' )
WHERE  ( `vobject`.`type` = 1 )
GROUP  BY `vobject`.`uuid`
HAVING Count('`vobject`.`uuid`') >= 2
LIMIT  10;

Hurray, it works! But the count is broken now, it has a value of '2'. This query returns 35 rows (the previous query returns the same number of rows) and each row has count equal to '2'.

This happens because of the GROUP BY clause:

SELECT Count(`vobject`.`uuid`) AS `count`
FROM   `vobject`
       INNER JOIN `vobject_tag_to_vobject` AS
                  `vobject_tags.vobject_tag_to_vobject`
               ON `vobject`.`uuid` =
       `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
       INNER JOIN `vobject_tag` AS `vobject_tags`
               ON `vobject_tags`.`id` =
`vobject_tags.vobject_tag_to_vobject`.`tag_id`
                  AND `vobject_tags`.`name` IN ( 'iron', 'tefal' )
WHERE  ( `vobject`.`type` = 1 )
GROUP  BY `vobject`.`uuid`
HAVING Count('`vobject`.`uuid`') >= 2;

To make this work we need to move the main query into subquery:

SELECT COUNT(*) as count FROM (
    SELECT `vobject`.`uuid` AS `count`
    FROM   `vobject`
           INNER JOIN `vobject_tag_to_vobject` AS
                      `vobject_tags.vobject_tag_to_vobject`
                   ON `vobject`.`uuid` =
           `vobject_tags.vobject_tag_to_vobject`.`vobject_uuid`
           INNER JOIN `vobject_tag` AS `vobject_tags`
                   ON `vobject_tags`.`id` =
    `vobject_tags.vobject_tag_to_vobject`.`tag_id`
                      AND `vobject_tags`.`name` IN ( 'iron', 'tefal' )
    WHERE  ( `vobject`.`type` = 1 )
    GROUP  BY `vobject`.`uuid`
    HAVING Count('`vobject`.`uuid`') >= 2
) as tmp;

Modify DAOFactory.prototype.count

options.attributes = [
    [this.sequelize.fn('COUNT', col), 'count']
]
// change to 
options.count = true;
options.attributes = [col]

Modify QueryGenerator.selectQuery

// add before the end of query
if(options.count) {
    query = 'SELECT COUNT(*) as count FROM (' + query + ') as tmp';
} 

query += ";";

Now everything works as it should :)

[mickhansen]

Very specific solution, but glad you worked it out for your problem :)

[ranwawa]

啊呀......