1

I'm working on a web app using React, Node, Express, Massive, and PostgreSQL, and having trouble performing one specific query:

SELECT 
  COUNT(DISTINCT cu.user_id) AS ucount, 
  COUNT(DISTINCT p.project_id) AS pcount, 
  COUNT(DISTINCT t.task_id) AS tcount, 
  c.clique_id, c.clique_name, c.admin_id, c.created_on 
FROM cliques c 
FULL OUTER JOIN cliques_users cu ON cu.clique_id = c.clique_id 
FULL OUTER JOIN users u ON u.user_id = cu.user_id 
FULL OUTER JOIN projects p ON p.clique_id = cu.clique_id 
FULL OUTER JOIN tasks t ON t.clique_id = cu.clique_id 
WHERE c.clique_id IN ( 
  SELECT cu.clique_id FROM cliques_users cu WHERE cu.user_id = 3 
) 
GROUP BY c.clique_id;

**Note: I'm using 3 on the subquery just to test.

I'm using Postico to test my SQL statements, and this query returns the results I expect. But when this is done on the app itself, requesting the data by hitting an endpoint, the server throws an error:

{ error: function     count(integer) does not exist at ...
name: 'error',
length: 225,
severity: 'ERROR',
code: '42883',
detail: undefined,
hint: 'No function matches the given name and argument types. You might need to add explicit type casts.',
position: '55',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_func.c',
line: '528',
routine: 'ParseFuncOrColumn' }

The callback function that runs when the endpoint is hit looks like this:

(req, res, next) => {
  req.app.get( 'db' )
    .clique.getCliqueSummaryQuery( req.params.user_id )
    .then( response => {
      res.status(200).json(response);
    })
    .catch( err => {
      console.log( 'getMyCliquesInfo failed: ', err );
      res.status(500).json( err );
    });
}

getCliqueSummaryQuery() runs the query, passing a url parameter as a variable that will replace the 3 I've hardcoded for testing. The error occurs with both variable and hardcoded values. I've copied the query straight from Postico to my sql file.

Anyone know why it works one way and not the other?

maurojflores
  • 176
  • 9
  • 1
    `SELECT ... c.clique_id, c.clique_name, c.admin_id, c.created_on ... GROUP BY c.clique_id` does not look like a valid PostgreSQL query.. Non aggregate columns (`c.clique_name, c.admin_id, c.created_on`) in the `SELECT` clause should be also used within the `GROUP BY` clause then your query is valid.. Let me geuss your worked with MySQL before using PostgreSQL where these kind of queries are allowed in some configs.(non running sql_mode ONLY_FULL_GROUP_BY) – Raymond Nijland Aug 23 '18 at 23:51
  • In other words: SELECTed expressions must either be in the `GROUP BY` list or be aggregate functions. – wildplasser Aug 23 '18 at 23:55
  • Hmm, just tested using GROUP BY on all the rest, and saw no change. Also tested getting rid of them leaving only the COUNTs. It seems it doesn't like what's being passed into count() - could it be it doesn't recognize it as a column name, and instead sees it as an integer? Thanks for the help, btw. – maurojflores Aug 24 '18 at 00:18
  • @RaymondNijland It can be valid if `c.clique_id` is primary key. – Łukasz Kamiński Aug 24 '18 at 06:15
  • Can't reproduce your problem. I'm guessing that your code is sending different query. Can you check Postgresql logs and paste query from there? – Łukasz Kamiński Aug 24 '18 at 06:28

0 Answers0