CODEWITHSUNDEEP
sqldatabasepostgresqlmodelsequelize.js
shivshankar
shivshankar

Reputation: 2135

find using model association

Dialect: postgres
Database version: @latest
Sequelize version: @latest

I'm trying to find out how to use an associate model. I've got 3 models: post, postCity and region. They have the following relation: postCity (post_id, region_id) associate to post (post_id) and region (region_id). I am using a search function like this:

include: [
    {
      model: models.postCity,
      include:[{model:models.region}],
      attributes: [[models.sequelize.fn('count', 'post_id'), 'count']],
    }
    ],
    where: {
    $or: [
        {
            "create_by" : {$not: 67}
        },
        {
       //   "postCities.region_name":{$iLike: "%Guangazhou2%"}
        },
        {
          "description":{$iLike: "%India%"}
        }
      ]
    }

which leads to:

SELECT "post"."post_id", "post"."description", "post"."create_by",
       "post"."create_time", "post"."update_time", "post"."country_id",
       "postCities"."post_id" AS "postCities.post_id",
       "postCities"."region_id" AS "postCities.region_id",
       "postCities"."order_no" AS "postCities.order_no",
       "postCities.region"."region_id" AS "postCities.region.region_id",
       "postCities.region"."region_name" AS "postCities.region.region_name",
       "postCities.region"."country_id" AS "postCities.region.country_id",
       "postCities.region"."province_id" AS "postCities.region.province_id"
FROM "t_post" AS "post"
   LEFT OUTER JOIN "t_post_city" AS "postCities"
      ON "post"."post_id" = "postCities"."post_id"
   LEFT OUTER JOIN "t_region" AS "postCities.region"
      ON "postCities"."region_id" = "postCities.region"."region_id"
WHERE ("post"."create_by" != 67 OR "post"."description" ILIKE '%India%');

When I uncomment "postCities.region_name":{$iLike: "%Guangazhou2%"} then I get this error

column post.postCities.region_name does not exist

I simply like to my query to be like this

... WHERE ("post"."create_by" != 67
           OR "post"."description" ILIKE '%India%'
           OR "postCities.region_name" ILIKE: "%Guangazhou2%")

Update

I also tried to include [{model:models.region, where:{"region_name":{$iLike: "%Guangazhou2%"}}}] but this doesn't give me the appropriate result.

Upvotes: 0

Views: 66

Answers (1)

Yrysbek Tilekbekov
Yrysbek Tilekbekov

Reputation: 2775

In order to add condition to included tables, you should wrap condition with $ symbol, like it:

include: [{
  model: models.postCity,
  include:[{model:models.region}],
  attributes: [[models.sequelize.fn('count', 'post_id'), 'count']],
}],
where: {
    $or: [{
        "create_by" : {$not: 67}
    }, {
        "$postCities.region.region_name$":{$iLike: "%Guangazhou2%"}
    }, {
        "description":{$iLike: "%India%"}
    }]
}

Upvotes: 1

Related Questions