SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    postgresql: adding an array function to an existing query

    I have have a query that returns rows from a blog database that can match a number of different supplied criteria. The query works as expected to return the data I require.

    A typical example of the sql:

    Code SQL:
    SELECT cr.category_id
           , c.name AS category_name
           , p.post_id
           , p.added
           , p.title
           , ts_headline(ts.title, ts.ts_query) AS headline
           , ts.ts_query
           , ts.rank FROM (
                  SELECT post_id
                         , title
                         , url
                         , added
                         , ts_query
                         , ts_rank_cd(search_tsv, ts_query) AS rank
                  FROM public.posts, plainto_tsquery('this is a test post') AS ts_query
                  WHERE search_tsv @@ ts_query ORDER BY rank DESC ) AS ts
     
    INNER
         JOIN public.category_relationships AS cr
         ON cr.post_id = ts.post_id
     
    INNER
         JOIN public.categories AS c
         ON cr.category_id = c.category_id
     
    INNER
         JOIN public.posts AS p
         ON cr.post_id = p.post_id
     
    WHERE cr.category_id = 9
      AND
           p.added BETWEEN '2010-12-01 00:00:00' AND '2010-12-31 23:59:59'
      AND
          p.post_id IN (
              SELECT post_id
              FROM public.tags
              WHERE tag = 'tag1' OR tag = 'tag2' OR tag = 'tag3')
     
    ORDER BY ts.rank, p.added DESC

    While I can search for a blog post using the above based on tags as part of the search criteria, I do not include the tags in the returned data since I will get a row of data for a certain post_id for each tag that exists for that post_id.

    What I would like to do is include the following as a subquery for returning a formatted string containing the tags for the relevant post:

    Code SQL:
    SELECT array_to_string(
           array(
                  SELECT tag FROM public.tags WHERE post_id = p.post_id
           ), ', ')
          AS tags

    Of course, the error is obvious: the alias 'p' does not exist in the subquery scope. My dilemma is how do I incorporate this subquery into the larger query above and pull the p.post_id into the subquery? I'm sure I'm just being obtuse here, but any thoughts would be appreciated
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  2. #2
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I discovered that I am using the wrong query with that particular subquery. Sorry to anyone who pondered this for me. While I would still like to be able to use that subquery with the original sql I posted (where the post_id may or may not be known), the query I needed to run on the database is below (the context of which the post_id is always known):

    Code SQL:
    SELECT p.*
          , cr.category_id
          , array_to_string(
                 array(
                        SELECT tag FROM public.tags WHERE post_id = 8
                 ),
                 ', '
            ) AS tags
     
    FROM public.posts AS p
     
    INNER
         JOIN public.category_relationships AS cr
         ON cr.post_id = p.post_id
     
    WHERE
          p.enabled IS TRUE
    AND
          p.post_id = 8;
    returns the data exactly as I need it. Thanks again to anyone who looked at this
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •