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
 

> El miércoles, 7 de marzo de 2018 7:35:18 (CET) Jan Hebler escribió: 
> > 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 
> > 
> > Am Dienstag, 6. März 2018 12:07:41 UTC+1 schrieb 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 
> > > 
> > > El lunes, 5 de marzo de 2018 15:30:55 (CET) Jan Hebler escribió: 
> > > > 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 
>
>
>
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"ActiveScaffold : Ruby on Rails Gem" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to activescaffold+unsubscr...@googlegroups.com.
To post to this group, send email to activescaffold@googlegroups.com.
Visit this group at https://groups.google.com/group/activescaffold.
For more options, visit https://groups.google.com/d/optout.

Reply via email to