Query function won't take rows with given default value

Question

I have only recently delved into the depths of PostgreSQL and have finally hit a snag. There are 3 tables, Notes, Tags, and Notes_Tags (Many to Many). Some Notes do not contain tags, but still need to be displayed. Maybe there is something that I have a poor understanding of that is not quite apparent to me, but I can't seem to figure out how to get it to display Notes that do not have Tags.

Here is the query I've built:

    select n.id, n.created_at, n.title, n.body, 
    case count(t.name) when 0 then '{}' else array_agg(t.name) end as noteTags
    from notes_tags nt
    inner join tags t on t.id = nt.tag_id
    left outer join notes n on n.id = nt.note_id
    where n.user_id = userId and
      case 
        when tagFilter = 'all' or tagFilter = '' then t.name != ''
            else t.name = tagFilter
        end
    group by n.created_at, n.title, n.body, n.id
    order by n.created_at DESC
    limit note_limit;

score 1 · Accepted Answer · answered Dec 07 '14 at 23:30

If you want to display the Notes that do not have Tags you have to replace the left outer join for right outer join since you want to keep all the registers from the right table of the join.

So if the rest of your query is correct this one should be what you are looking for

select n.id, n.created_at, n.title, n.body, 
case count(t.name) when 0 then '{}' else array_agg(t.name) end as noteTags
from notes_tags nt
inner join tags t on t.id = nt.tag_id
right outer join notes n on n.id = nt.note_id
where n.user_id = userId and
  case 
    when tagFilter = 'all' or tagFilter = '' then t.name != ''
        else t.name = tagFilter
    end
group by n.created_at, n.title, n.body, n.id
order by n.created_at DESC
limit note_limit;

Query function won't take rows with given default value

1 Answers1