0

I wrote this view, and it is selecting almost everything I want, but I do not get results for all: project_keywords(which has_many project_report(keyword_id, project_id))

So a project_keyword has a data saved into project_report and the data is stored into position column but there are times when there is no data saved for a project_keyword.

When there is no data saved, I would like to add 0 into array_agg(pr.position).

 SELECT  
  k.name, 
  pr.project_id,
  array_agg(distinct pr.position) as pr_position,
  date(pr.created_at)
FROM 
  project_keywords pk
INNER JOIN keywords k on pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
WHERE 
  date(pr.created_at) = '2014-03-27' AND pr.project_id = 2
GROUP BY k.name, pr.project_id, date(pr.created_at);

Also how can return results where array_agg(distinct pr.position) SIZE(no of elements) is > 1

Edit:

SELECT  
  k.name, 
  pr.project_id,
  COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]),
  date(pr.created_at)
FROM 
  project_keywords pk
INNER JOIN keywords k on pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
WHERE 
  date(pr.created_at) = '2014-03-27' AND pr.project_id = 2
GROUP BY k.name, pr.project_id, date(pr.created_at)
HAVING array_length(COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]), 1) > 1;

This query is returning all the project_keywords which have project_reports row's, but they are not returning 0 for project_keywords which do not have associated rows in project_reports.

  • Maybe add a case when end statement in the array_agg() function? Some thing like array_agg(case when pr.position is not null then distinct pr.position else 0 end) – Fabrizio Mazzoni Mar 27 '14 at 14:35
  • I tried that, but is not working. – RockNinja Mar 27 '14 at 14:55
  • 1
    Please define: but there are times when there is no data saved for a project_keyword What do you safe exactly? NULL? An empty string? No row? Table definitions or even a test case (do you know SQL Fiddle?) would be swell for such a question: we could see NOT NULL or FK constraints, etc. and wouldn't have to do all the guesswork for you. The relevant parts of what \d tbl gives you in psql. – Erwin Brandstetter Mar 28 '14 at 0:10
3

For the first point, you can try this:

COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[])

The same can be achieved by a CASE, too:

CASE WHEN array_agg(id) IS NULL
     THEN '{0}'::integer[] 
     ELSE array_agg(id) END

The second problem can be handled by a HAVING cause:

...
GROUP BY ...
HAVING array_length(COALESCE(array_agg(DISTINCT pr.position), '{0}'::integer[]), 1) > 1;
  • it still doesn't return 0, if there is project_report record associated with project_keywords – RockNinja Mar 27 '14 at 17:12
  • @user36209 What does it return then? – dezso Mar 27 '14 at 18:49
  • If add a condition to return only results for a project_keywords which I know that it doesn't have any row in project_report, the return is empty – RockNinja Mar 28 '14 at 9:04
  • @user36209 could you show that query please? – dezso Mar 28 '14 at 10:56
  • please check my post, under Edit. – RockNinja Mar 28 '14 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.