I have a table in which I want to store images. Each image has arbitrary properties that I want to store in a key-value table.

The table structure looks like this

Now I want a query to find all pictures made by an iphone I could to something like this

select
    fk_picture_id
from
    my_table
where
    key = 'camera'
    and
    value = 'iphone';

This works without any problems. But as soon as I want to add another key to my query I am get stucked. Lets say, I want all pictures made by an iPhone in the year 2020, I can not do something like

select
    distinct(fk_picture_id)
from
    my_table
where
    (
        key = 'camera'
        and
        value = 'iphone'
    )
    or
    (
        key = 'year'
        and
        value = '2020'
    )

...because this selects the id 1, 4 and 5.

At the end I might have 20 - 30 different criteria to look for, so I don't think some sub-selects would work at the end.

I'm still in the design phase, which means I can still adjust the data model as well. But I can't think of any way to do this in a reasonable way - except to include the individual properties as columns in my main table.

CodePudding user response：

A pattern you can consider here is to build a table of search parameters, then simply join this to your target table.

You would first create a temporary table with key and value columns then insert into it the search criteria values, any number of values you wish.

Using a CTE in place of a temporary table might look like:

with s as (
    select 'camera' key, 'iphone' value
    union all
    select 'year', '2020'
)
select distinct t.fk_picture_id
from s
join t on t.key=s.key and t.value=s.value

CodePudding user response：

The solution I found - thanks to this article How to query data based on multiple 'tags' in SQL?

is that I made some changes to the database model

picture

And then I created a table for the tags

tag

And the cross table

picture_tag

For a better understanding of the datasets

Now I can use the following statement

SELECT *
  FROM picture
  INNER JOIN (
   SELECT fk_picture_id
   FROM picture_tag
   WHERE fk_tag_id IN (100, 101)
   GROUP BY fk_picture_id
   HAVING COUNT(fk_tag_id) = 2
  ) AS picture_tag
 ON picture.id = picture_tag.fk_picture_id;

The only thing I need to do before the query is to collect the IDs of the tags I want to search for and put the number of tags in the having count statement.

If someone needs the example data, here are the sql statements for the tables and data

 create table picture (
   id integer,
   name char(100)
 );

 create table tag (
  id integer,
  tag char(100)
 );

 create table picture_tag (
  fk_picture_id integer,
  fk_tag_id integer
 );

 insert into picture values (1, 'Picture 1');
 insert into picture values (2, 'Picture 2');

 insert into tag values (100, 'Germay');
 insert into tag values (101, 'iphone');
 insert into tag values (102, 'United States');

 insert into picture_tag values (1, 100);
 insert into picture_tag values (1, 101);
 insert into picture_tag values (2, 101);
 insert into picture_tag values (2, 102);