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.
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.
