PostgreSQL: PDOexception error when creating or saving nodes

You know I actually tried the code below to see what would happen just before I posted the ticket. (which I believe would be the same thing you suggested, but if I'm wrong please let me know.) :

->groupBy('u.uid')->groupBy('u.name')->groupBy('u.language');

PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "s.module" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...function, .....

The error was basically the same thing just indicating the 's' table starting with the 's.module' column (see below). So I thought it might be a little deeper than that.

Unfortunately I cannot test your suggestion at this moment because I can't connect to my database which only works internally at the office. I will see if I can load up an old database dump to it try out.

I really appreciate the quick response!!!

Ok I have tried both options. No Luck. The groupBy() on each field gave me something new however...

• First I went with the ->groupBy() on each field:
  line# 406 in subscriptions.module

->groupBy('u.uid')->groupBy('u.name')->groupBy('u.language')->groupBy('s.module')->groupBy('s.field')
->groupBy('s.value')->groupBy('s.author_uid')->groupBy('s.send_interval')->groupBy('su.digest')
->groupBy('su.suspended')->groupBy('sls.last_sent');

Returns this error:
PDOException: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval of sequence "subscriptions_queue_sqid_seq" is not yet defined in this session: INSERT INTO subscriptions_queue (uid, name, language, module, field, value, author_uid, send_interval, digest, suspended, last_sent, load_function, load_args, is_new) SELECT u.uid AS uid, u.name AS name, u.language AS language, s.module AS module, s.field AS field, s.value AS value, s.author_uid AS author_uid, s.send_interval AS send_interval, su.digest AS digest, su.suspended AS suspended, COALESCE(sls.last_sent, 0) AS last_sent, 'subscriptions_content_load_node' AS load_function, '584' AS load_args, 1 AS is_new FROM subscriptions s INNER JOIN subscriptions_user su ON s.recipient_uid = su.uid INNER JOIN users u ON su.uid = u.uid LEFT OUTER JOIN subscriptions_last_sent sls ON su.uid = sls.uid AND s.send_interval = sls.send_interval INNER JOIN taxonomy_index tn ON s.value = CAST(tn.tid AS VARCHAR) WHERE (s.module = :db_condition_placeholder_0) AND (s.field = :db_condition_placeholder_1) AND (s.author_uid IN (:db_condition_placeholder_2, :db_condition_placeholder_3)) AND (tn.nid = :db_condition_placeholder_4) GROUP BY u.uid, u.name, u.language, s.module, s.field, s.value, s.author_uid, s.send_interval, su.digest, suspended, sls.last_sent, tn.nid; Array ( ) in subscriptions_queue() (line 438 of /sites/all/modules/contrib/subscriptions/subscriptions.module).

• Next I went with distinct():
  line# 406 in subscriptions.module

->distinct();

Returns this error:
PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "u.uid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...load_function, load_args, is_new) SELECT DISTINCT u.uid AS u... ^: INSERT INTO subscriptions_queue (uid, name, language, module, field, value, author_uid, send_interval, digest, suspended, last_sent, load_function, load_args, is_new) SELECT DISTINCT u.uid AS uid, u.name AS name, u.language AS language, s.module AS module, s.field AS field, s.value AS value, s.author_uid AS author_uid, s.send_interval AS send_interval, su.digest AS digest, su.suspended AS suspended, COALESCE(sls.last_sent, 0) AS last_sent, 'subscriptions_content_load_node' AS load_function, '585' AS load_args, 1 AS is_new FROM subscriptions s INNER JOIN subscriptions_user su ON s.recipient_uid = su.uid INNER JOIN users u ON su.uid = u.uid LEFT OUTER JOIN subscriptions_last_sent sls ON su.uid = sls.uid AND s.send_interval = sls.send_interval INNER JOIN taxonomy_index tn ON s.value = CAST(tn.tid AS VARCHAR) WHERE (s.module = :db_condition_placeholder_0) AND (s.field = :db_condition_placeholder_1) AND (s.author_uid IN (:db_condition_placeholder_2, :db_condition_placeholder_3)) AND (tn.nid = :db_condition_placeholder_4) GROUP BY tn.nid; Array ( ) in subscriptions_queue() (line 438 of /sites/all/modules/contrib/subscriptions/subscriptions.module).

• Then I tried to pass in the u.uid in distinct():
  line# 406 in subscriptions.module

Then I tried to pass in the u.uid in distinct():
line# 406 in subscriptions.module

->distinct('u.uid');

Returns this error (basically the same as above):
PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "u.uid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...load_function, load_args, is_new) SELECT DISTINCT u.uid AS u... ^: INSERT INTO subscriptions_queue (uid, name, language, module, field, value, author_uid, send_interval, digest, suspended, last_sent, load_function, load_args, is_new) SELECT DISTINCT u.uid AS uid, u.name AS name, u.language AS language, s.module AS module, s.field AS field, s.value AS value, s.author_uid AS author_uid, s.send_interval AS send_interval, su.digest AS digest, su.suspended AS suspended, COALESCE(sls.last_sent, 0) AS last_sent, 'subscriptions_content_load_node' AS load_function, '586' AS load_args, 1 AS is_new FROM subscriptions s INNER JOIN subscriptions_user su ON s.recipient_uid = su.uid INNER JOIN users u ON su.uid = u.uid LEFT OUTER JOIN subscriptions_last_sent sls ON su.uid = sls.uid AND s.send_interval = sls.send_interval INNER JOIN taxonomy_index tn ON s.value = CAST(tn.tid AS VARCHAR) WHERE (s.module = :db_condition_placeholder_0) AND (s.field = :db_condition_placeholder_1) AND (s.author_uid IN (:db_condition_placeholder_2, :db_condition_placeholder_3)) AND (tn.nid = :db_condition_placeholder_4) GROUP BY tn.nid; Array ( ) in subscriptions_queue() (line 438 of /sites/all/modules/contrib/subscriptions/subscriptions.module).

1. For the 1st test. I actually left off the 'sls.last_sent' the first time but then the error was the exact same as before but it was looking for the 'sls.last_sent' column.

   PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "sls.last_sent" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...function, .....

   So I added ->groupBy('sls.last_sent') to the code then I received the new error. So I figured that I would post that for you. I will try do another b adding the others you suggest as well and see what happens. It might take me a little bit. I need to get it running locally on my machine since I'm not at the office.

2. for #2. I did completely replace the ->groupBy() with ->distinct(). However I see a little further down there is this:

   if (!empty($query['groupby'])) {
             $select
               ->groupBy($query['groupby']);
           }
   

   Do I need to replace that as well?

Hello -

Sorry about the delay in responses. I was out of town for a long weekend.

I have updated the groupBy() to what you suggested and I also removed the other IF statement mentioned above:

->groupBy('u.uid')->groupBy('u.name')->groupBy('u.language')        ->groupBy('s.module')->groupBy('s.field')->groupBy('s.value')->groupBy('s.author_uid')->groupBy('s.send_interval')
->groupBy('su.digest')->groupBy('su.suspended')
->groupBy('last_sent')->groupBy('load_function')->groupBy('load_args')->groupBy('is_new');

Here is the error I get:
PDOException: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval of sequence "subscriptions_queue_sqid_seq" is not yet defined in this session: INSERT INTO subscriptions_queue (uid, name, language, module, field, value, author_uid, send_interval, digest, suspended, last_sent, load_function, load_args, is_new) SELECT u.uid AS uid, u.name AS name, u.language AS language, s.module AS module, s.field AS field, s.value AS value, s.author_uid AS author_uid, s.send_interval AS send_interval, su.digest AS digest, su.suspended AS suspended, COALESCE(sls.last_sent, 0) AS last_sent, 'subscriptions_content_load_node' AS load_function, '588' AS load_args, 1 AS is_new FROM subscriptions s INNER JOIN subscriptions_user su ON s.recipient_uid = su.uid INNER JOIN users u ON su.uid = u.uid LEFT OUTER JOIN subscriptions_last_sent sls ON su.uid = sls.uid AND s.send_interval = sls.send_interval INNER JOIN taxonomy_index tn ON s.value = CAST(tn.tid AS VARCHAR) WHERE (s.module = :db_condition_placeholder_0) AND (s.field = :db_condition_placeholder_1) AND (s.author_uid IN (:db_condition_placeholder_2, :db_condition_placeholder_3)) AND (tn.nid = :db_condition_placeholder_4) GROUP BY u.uid, u.name, u.language, s.module, s.field, s.value, s.author_uid, s.send_interval, su.digest, su.suspended, last_sent, load_function, load_args, is_new; Array ( ) in subscriptions_queue() (line 442 of /sites/all/modules/contrib/subscriptions/subscriptions.module).

I'm still working on it. At the moment I'm trying to get the SQL to actually put something in the subscriptions_queue table. What I have found out is that it seems to have an issue with the taxonomy_index table not having matching values in the 'value' column. Here is the SQL statement I have been running/working with:

I have a couple items commented out (--) once I do that on those lines I get results. Once I have the taxonomy references uncommitted I get no results.

--INSERT INTO subscriptions_queue 
SELECT 
	u.uid AS uid, 
	u.name AS name, 
	u.language AS language, 
	s.module AS module, 
	s.field AS field, 
	s.value AS value, 
	s.author_uid AS author_uid, 
	s.send_interval AS send_interval, 
	su.digest AS digest,
	'628' AS load_args,
	'subscriptions_content_load_node' AS load_function,
	1 AS is_new,
	COALESCE(sls.last_sent, 0) AS last_sent, 
	su.suspended AS suspended
FROM subscriptions s 
	INNER JOIN subscriptions_user su ON s.recipient_uid = su.uid 
	INNER JOIN users u ON su.uid = u.uid 
	LEFT OUTER JOIN subscriptions_last_sent sls ON su.uid = sls.uid AND s.send_interval = sls.send_interval 
	--INNER JOIN taxonomy_index tn ON s.value = CAST(tn.tid AS VARCHAR) 
WHERE (s.module = 'node') 
	AND (s.field = 'tid') 
	AND (s.author_uid IN (1, -1)) 
	--AND (tn.nid = 629) 
GROUP BY 
	u.uid, u.name, u.language, s.module, s.field, s.value, s.author_uid, s.send_interval, su.digest, su.suspended, last_sent--, tn.nid

Well it's now working. 2 things.

• The following code needs to replace the ->groupBy(u.uid); . We were on the right track:
  

  ->groupBy('u.uid')->groupBy('u.name')->groupBy('u.language')
  ->groupBy('s.module')->groupBy('s.field')->groupBy('s.value')->groupBy('s.author_uid')->groupBy('s.send_interval')
  ->groupBy('su.digest')->groupBy('su.suspended')
  ->groupBy('last_sent');
  

• The other part that was that in order for the content to publish there needed to be a user already subscribed to content. So for the function subscriptions_queue(array $event) { function maybe there should be some check to see if there is subscribers, if not skip the $insert->execute();

I think the later part is why I though there was an issue with the taxonomy stuff that I mentioned in the post above.