Thursday 18 May 2017

Omitting some rows where a userid exists from child association

I have four tables in a MySQL database.

  1. UserTable

    • UserId
    • Name
    • etc
  2. ArticleTable

    • ArticleId
    • UserId (fk)
    • etc
  3. ArticleItemTable

    • ArticleItemId
    • ArticleId (fk)
    • etc
  4. UserArticleInterest

    • UserArticleInterestId
    • ArticleItemId (fk)
    • UserId (fk)

ArticleTable is a direct parent of ArticleItemTable, where the ArticleTable ID is present in ArticleItemTable (1:m).
UserArticleInterest is a sort of intersection/data table where the User ID and Article Item ID resides.

I use Sequelize on my API-layer to select and insert data, and it's working very good, but I am stuck on a small issue I can't wrap my head around for some reason.

On the front end, I don't want a user to be able to see ArticleItems he has already registered interests for.

This is the Sequelize query I wrote, but it's omitting everything for an Article if an interest is already registered:

Article.findOne({
    where: {
        articleid: articleid
    },
    include: [{
        model: ArticleItem,
        required: true,
        include: [{
            model: UserArticleInterest,
            required: true,
            where: {
                userid: {
                    $ne: userid
                }
            }
        }],
    }]
})

I thought associations would be smart enough to only omit the ArticleItems where there is a UserArticleInterest record, but I just think I can't really wrap my head around it.

How can I only get ArticleItems (I need the Article data as well) the user has not already registered in UserArticleInterest (I know how I could do it in spaghetti code, but I'm pretty certain there is something I'm missing with Sequelize that can make it cleaner)?



via NicT

No comments:

Post a Comment