magosi

magosi

Hi. In my application I have a many to many association between Product and Tag schemas which goes through the ProductTag table. Quite straight forward, however, I need to list all products that have ALL the tags in a given list. I really cannot find any answer as to how one can do this. This is what I have so far:

def list_category_products(categories) do
        from(
            p in Product,
            join: pt in ProductTag, on: pt.product_id == p.id,
            join: t in Tag, on: pt.tag_id == t.id,
            where: t.name in ^categories
        )
        |> Repo.all()
    end

Lets assume I have product1 associated with the tag “cake” and product2 associated with the tag “tea”. If I pass only “cake” or only “tea” to the function, I get the correct product, but if I pass both “cake” and “tea”, I get both products where I expect to receive none as none of my products have both tags.

I will appreciate any help or advice. Thank you all in advance.

Showing Posts 1 to 2

fuelen

fuelen

Hi David,
List of product ids can be retrieved using this query (I didn’t test):

from tags in Tag,
  join: products_tags in ProductTag, on: products_tags.tag_id == tags.id,
  where: tags.name in ^categories,
  select: products_tags.product_id,
  group_by: products_tags.product_id,
  having: count() == ^length(categories)

After that you can get list of products using subquery or just separate query

magosi

magosi OP

Thank you very much. I will try it now.

— All posts loaded —

Where Next? Top

Trending in Questions Top

RSP87
I’m working on a project that simulates the bumbl example in the programming phoenix book. It acts almost like an email client. We have a...
New
nseaSeb
Hello, I know there is an approach for handling lists that allows for optimized traversal, but I can’t recall the specific method (somet...
New
brecabral
Documentation While reading the Scoped Routes section, I noticed that the documentation currently refers to a problem without explainin...
New
RemyXRenard
I’m seeing that a list inside a Kino.DataTable will be interpreted as a charlist, even if the Kino.configure() is set to charlists: :as_l...
New
velrest
So my question is quite simple and i have found no conclusive answer on forum, google or AI. Should we use :erlang.float for Integer to ...
New
samoloth
Hi, I’ve just set up an application with ash_authentication. There is only magic link strategy for now, so there is no confirmation add o...
New
asweet-confluent
I recently noticed that Elixir’s Logger defaults its primary log level to :debug when no :logger, :level application configuration is pre...
New

Other Trending Topics Top

JesseHerrick
Hey, I’m Jesse and I’m the main contributor behind Dexter, a full-featured, lightning-fast Elixir LSP optimized for large codebases. It s...
New
mudasobwa
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
New
marciok
Hi there! We created Gust: A task orchestrator inspired by Airflow. For those who have never heard about Aiflow, it’s a Python-based wor...
New
mhanberg
Hi everyone! The first release candidate for the Expert language server project is now available! We’ve published a press release detai...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
Dmk
Xamal is a deployment tool for Elixir apps that deploys native releases to bare metal servers over SSH. It’s a port of GitHub - basecamp/...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews