On Tue, Oct 19, 2021 at 2:15 AM David Espada <brome...@gmail.com> wrote:
> El miércoles, 13 de octubre de 2021 a las 15:43:39 UTC+2, Jeremy Evans
> escribió:
>
>> On Wed, Oct 13, 2021 at 1:49 AM David Espada <brom...@gmail.com> wrote:
>>
>>> Hi.
>>>
>>> I need to define a synthtic field for a Sequel::Model source dataset.
>>> I'd like getting a sum field with any Model query. How can it be done?
>>>
>>
>> It sounds like you want to add a SELECTed expression to the model's
>> dataset:
>>
>> class ModelName < Sequel::Model(DB[:table].select_append(some_expression))
>> end
>>
>
> Thank you very much :D
>
> I have found a problem with my "cooked" dataset. Here is the code for
> composing it:
>
> --------------------------------------------------------
> class Gasto < Sequel::Model
> set_dataset(dataset.select(*dataset.columns.map {|c|
> Sequel.qualify(:gastos, c)}).
> left_join(dataset.exclude(id: :gastos__id).as('g'),
> prevision_id: :gastos__prevision_id).
> select_append {sum(:g__importe_divisa).as(:otros_gastos)}.
> group_by(:gastos__id))
> end
> --------------------------------------------------------
>
> When I try to save data on this model here is the result of "puts
> dataset.sql":
>
> ---------------------------------------------------------
> SELECT * FROM (SELECT "gastos"."id", "gastos"."factura_proveedor_id",
> "gastos"."prevision_id", "gastos"."divisa_id", "gastos"."cambio_gasto",
> "gastos"."importe_divisa_base", "gastos"."importe_divisa",
> "gastos"."cuenta_gasto", "gastos"."dato_facturacion_compra_id",
> "gastos"."concepto_id", "gastos"."contraccion_id", "gastos"."_type",
> "gastos"."codigo_analitica", "gastos"."parent_id", "gastos"."repartido",
> "gastos"."entidad_reparto", "gastos"."criterio_reparto_id",
> "gastos"."tipo_entidad", "gastos"."clave_busqueda_reparto_id",
> "gastos"."valor_reparto_id", sum("g"."importe_divisa") AS "otros_gastos"
> FROM "gastos" LEFT JOIN (SELECT * FROM "gastos" WHERE (("gastos"."_type" IN
> ('factura_proveedor_gasto')) AND ("id" != "gastos"."id"))) AS "g" ON
> ("g"."prevision_id" = "gastos"."prevision_id") WHERE ("gastos"."_type" IN
> ('factura_proveedor_gasto')) GROUP BY "gastos"."id") AS "gastos" LIMIT 1;
> ----------------------------------------------------------
>
> It works good when executed directly in PostgreSQL. But when doing the
> save in Sequel it raises the error:
>
> ------------------------------------------------------------
> Sequel::DatabaseError: Java::OrgPostgresqlUtil::PSQLException: ERROR:
> syntax error at or near "("
> Position: 13
> from
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
> Caused by Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at
> or near "("
> Position: 13
> from
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
> Caused by Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at
> or near "("
> Position: 13
> from
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2532)
> -------------------------------------------------------------
>
> Is there a bug? Anything I do wrong?
>
Not a bug. You cannot insert into a subquery, and Sequel::Model
automatically wraps a dataset in a subquery in cases where it uses a JOIN,
to prevent other issues.
You may be able to do what you want by creating a view, and having the
model use that view as the dataset (PostgreSQL supports updatable views).
Alternatively, you could try:
class Gasto < Sequel::Model
plugin :split_values
orig_columns = columns
orig_instance_dataset = @instance_dataset
set_dataset ...
@instance_dataset = orig_instance_dataset
@columns = orig_columns
end
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSf_WV85565zsbsTaQcO6rhcbjcXp5zMzhjHO6nDvS0cvQ%40mail.gmail.com.
