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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/activescaffold.
For more options, visit https://groups.google.com/d/optout.
