PostgreSQL match list of id's

Question

I'm expanding my select of media items with some filters which one of them is category. The relation between media and category is many-to-many which meens that I have a relation table media_categories. I'm sending a categories filter in form of integer[] with category id's. How do I match two arrays to get the common elements?

Here is my stored procedure: (see comment in the code)

CREATE OR REPLACE FUNCTION mediabase.select_media(sysenvironment character varying, statusid integer, wildcard character varying, categoryIds integer[])
  RETURNS refcursor AS
$BODY$
    DECLARE ref refcursor;

    BEGIN
      OPEN ref FOR 

    SELECT 
        media.id, 
        media.title, 
        media.unique_filename, 
        media.owner_id, 
        media.status_id, 
        media.location_name_id, 
        media.upload_user_id, 
        media.upload_ip, 
        media.metadata_id, 
        media.type_id, 
        media.description, 
        media.system_environment, 
        media.upload_date, 
        media.gps_location, 
        media.language_id, 
        (SELECT ARRAY (SELECT publication_id FROM mediabase.media_publications WHERE media_id = media.id)) as publication_ids,
        media.limitations, 
        (SELECT ARRAY (SELECT category_id FROM mediabase.media_categories WHERE media_id = media.id)) as category_ids,
        (SELECT ARRAY (SELECT keyword_id FROM mediabase.media_keywords WHERE media_id = media.id)) as keyword_ids,
        media.credits,
        metadata.width, 
        metadata.height, 
        metadata.equipment, 
        metadata.copyright, 
        metadata.creation_time, 
        metadata.file_format, 
        metadata.resolution, 
        metadata.resolution_unit, 
        metadata.gps_longitude, 
        metadata.gps_latitude, 
        metadata.artist, 
        metadata.color_space, 
        metadata.gps_altitude, 
        metadata.software_used, 
        metadata.user_comment
    FROM 
        mediabase.media, 
        mediabase.metadata
    WHERE media.metadata_id = metadata.id
    AND (media.status_Id = statusId OR statusId = -1)
    AND media.system_environment = sysEnvironment
    AND (lower(media.title) LIKE lower('%'||lower(wildcard)||'%') OR lower(media.description) LIKE lower('%'||lower(wildcard)||'%') OR lower(metadata.artist) LIKE lower('%'||lower(wildcard)||'%'))
    -- Problem start
    -- in the following line I'm trying to make the match with no success
    AND (SELECT ARRAY (SELECT category_id FROM mediabase.media_categories WHERE media_id = media.id)) IN (categoryIds) 
    -- Problem end
    ORDER BY media.upload_date DESC;

      RETURN ref;                       
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Thanks

Petar Donchev Marinov · Accepted Answer · 2013-01-23 09:39:06Z

1

Hm, I guess you are looking for postgre array function <@ (is contained by)

http://www.postgresql.org/docs/9.2/static/functions-array.html

The condition must look something like:

AND (SELECT ARRAY (SELECT category_id FROM mediabase.media_categories WHERE media_id = media.id)) <@ categoryIds

answered Jan 23, 2013 at 9:39

Petar Donchev Marinov

819 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ambran Over a year ago

Thanks. Last thing, I'm using the line SELECT ARRAY (SELECT category_id FROM mediabase.media_categories WHERE media_id = media.id) twice. Can you show me how to save it in a Integer[] variable which I can reuse in the query?

Petar Donchev Marinov Over a year ago

Try: DECLARE category_ids integer[]; ... SELECT ARRAY (SELECT category_id FROM mediabase.media_categories WHERE media_id = media.id) INTO category_ids;

Collectives™ on Stack Overflow

PostgreSQL match list of id's

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related