Is it possible to add group-clause to sort by?

[Jan Hebler]

Hi

I want to sort by an calculation on an associated table, i.E.


    config.columns[:findings].includes = [ :vulnerabilities ]
    config.columns[:findings].sort_by :sql => 'sum(vulnerabilities.score)'


This doesn't work as AS don't set the necessary "group by"-clause:

ActionView::Template::Error (PG::GroupingError: ERROR:  column "tbl_node.node_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  DISTINCT "tbl_node"."node_id", sum(vulnerabilities.s...
                         ^
: SELECT  DISTINCT "tbl_node"."node_id", sum(vulnerabilities.score) AS alias_0, "tbl_node"."node_id" AS alias_1 FROM "tbl_node" LEFT OUTER JOIN "findings" ON "findings"."system_id" = "tbl_node"."node_id" AND (findingstate_id != 4) LEFT OUTER JOIN "vulnerabilities" ON "vulnerabilities"."id" = "findings"."vulnerability_id" WHERE ('t'='t') ORDER BY sum(vulnerabilities.score) ASC, "tbl_node"."node_id" ASC LIMIT $1 OFFSET $2):


Is there any possibility to tell AS to set this clause?

Thanks, Jan

[Sergio Cambra]

You have to define custom_finder_options and add group options when
active_scaffold_config.list.user.sorting.sorts_on? :findings

def custom_finder_options
if active_scaffold_config.list.user.sorting.sorts_on? :findings
{group: 'tbl_node.node_id'}
else
{}
end
end

[Jan Hebler]

Thank You Sergio,

this works half the Way. AS seems to make two selects. The first selects some ID's for Pagination, i assume (and works with your suggestion):

SQL (214.1ms)  SELECT  DISTINCT "tbl_node"."node_id", sum(vulnerabilities.score) AS alias_0, "tbl_node"."node_id" AS alias_1 FROM "tbl_node" LEFT OUTER JOIN "findings" ON "findings"."system_id" = "tbl_node"."node_id" AND (findingstate_id != 4) LEFT OUTER JOIN "vulnerabilities" ON "vulnerabilities"."id" = "findings"."vulnerability_id" WHERE ('t'='t') GROUP BY tbl_node.node_id ORDER BY sum(vulnerabilities.score) ASC, "tbl_node"."node_id" ASC LIMIT $1 OFFSET $2  [["LIMIT", 33], ["OFFSET", 0]]

 but then AS makes an second select for the whole set of columns, and this select appends also the sort-clause and fails. Do i have to append the group - Statement to every column?

  SQL (306.1ms)  SELECT "tbl_node"."node_id" AS t0_r0, "tbl_node"."Node" AS t0_r1, "tbl_node"."Beschreibung" AS t0_r2, "tbl_node"."in_chassis" AS t0_r3, "tbl_node"."herst" AS t0_r4, "tbl_node"."typ" AS t0_r5, "tbl_node"."flag1" AS t0_r6, "tbl_node"."report" AS t0_r7, "tbl_node"."KPI" AS t0_r8, "tbl_node"."sun_rev" AS t0_r9, "tbl_node"."vrolle" AS t0_r10, "tbl_node"."veinsatz" AS t0_r11, "tbl_node"."vstatus" AS t0_r12, "tbl_node"."vstort" AS t0_r13, "tbl_node"."vmc" AS t0_r14, "tbl_node"."dr_stat" AS t0_r15, "tbl_node"."LDAP_Client" AS t0_r16, "tbl_node"."UK" AS t0_r17, "vulnerabilities"."id" AS t1_r0, "vulnerabilities"."title" AS t1_r1, "vulnerabilities"."score" AS t1_r2, "vulnerabilities"."created_at" AS t1_r3, "vulnerabilities"."updated_at" AS t1_r4, "vulnerabilities"."application" AS t1_r5 FROM "tbl_node" LEFT OUTER JOIN "findings" ON "findings"."system_id" = "tbl_node"."node_id" AND (findingstate_id != 4) LEFT OUTER JOIN "vulnerabilities" ON "vulnerabilities"."id" = "findings"."vulnerability_id" WHERE ('t'='t') AND "tbl_node"."node_id" IN (2899, 4308, 4314, 4337, 4388, 4389, 4498, 4499, 4500, 4501, 4504, 4505, 4507, 4508, 4509, 4510, 4513, 4514, 4515, 4518, 4525, 4531, 4913, 4920, 5934, 6092, 7353, 7530, 7736, 7955, 8137, 8189, 8246) GROUP BY tbl_node.node_id ORDER BY sum(vulnerabilities.score) ASC, "tbl_node"."node_id" ASC

 
Thank You,

Jan

[Sergio Cambra]

Hi Jan

What's the SQL error?

SQL standard requires to include every column in select in the group by, but
traditionally mysql didn't require it and it allowed to select more columns
than columns in group by clause. However newer versions of MySQL have strict
sql enabled, and don't allow it. If you are using MySQL, probably easier way
is disabling strict sql mode, so you don't have include every column in group
by clause. If you use a different db than mysql, you should check how it
works.

[Jan Hebler]

Am Mittwoch, 7. März 2018 12:19:47 UTC+1 schrieb Sergio Cambra:

Hi Jan

What's the SQL error?

ActionView::Template::Error (PG::GroupingError: ERROR:  column "tbl_node.Node" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "tbl_node"."node_id" AS t0_r0, "tbl_node"."Node" AS t...
 

SQL standard requires to include every column in select in the group by, but
traditionally mysql didn't require it and it allowed to select more columns
than columns in group by clause. However newer versions of MySQL have strict
sql enabled, and don't allow it. If you are using MySQL, probably easier way
is disabling strict sql mode, so you don't have include every column in group
by clause. If you use a different db than mysql, you should check how it
works.

Im using Postgres, so no Chance. My hope was that there is an way or option to tell AS to use the order of the ID's from the first query(which is correct) to display the data from the second query properly sorted. My next try will to add an subquery via the custom_finder_options and sort by the result. However, this ist not an esay task for me, so i will delay it for now and stay with the slow function-based sorting.

Thank You, Jan

[Sergio Cambra]

El jueves, 8 de marzo de 2018 6:36:43 (CET) Jan Hebler escribió:
> Am Mittwoch, 7. März 2018 12:19:47 UTC+1 schrieb Sergio Cambra:
> > SQL standard requires to include every column in select in the group by,
> > but
> > traditionally mysql didn't require it and it allowed to select more
> > columns
> > than columns in group by clause. However newer versions of MySQL have
> > strict
> > sql enabled, and don't allow it. If you are using MySQL, probably easier
> > way
> > is disabling strict sql mode, so you don't have include every column in
> > group
> > by clause. If you use a different db than mysql, you should check how it
> > works.
>
> Im using Postgres, so no Chance. My hope was that there is an way or option
> to tell AS to use the order of the ID's from the first query(which is
> correct) to display the data from the second query properly sorted. My next
> try will to add an subquery via the custom_finder_options and sort by the
> result. However, this ist not an esay task for me, so i will delay it for
> now and stay with the slow function-based sorting.

It's rails who is using 2 queries, ActiveScaffold will use a line like:

Model.includes(associations).group('tbl_node.node_id).order('sum(vulnerabilities.score)
ASC, "tbl_node"."node_id" ASC').limit(33).offset(0)

And rails issues 2 queries to db server. I don't know if there is a better way
to make queries with group and includes in rails.

Regards