Project

General

Profile

Forums » Help »

Wt::Dbo join -

Added by Marcelo Antunes 11 months ago

I have those 2 postgres tables:

CREATE TABLE IF NOT EXISTS public.a
(
    id bigint NOT NULL DEFAULT nextval('a_id_seq'::regclass),
    version integer NOT NULL,
    enable boolean NOT NULL,
    CONSTRAINT a_pkey PRIMARY KEY (id),

)


CREATE TABLE IF NOT EXISTS public.b
(
    id bigint NOT NULL DEFAULT nextval('b_id_seq'::regclass),
    version integer NOT NULL,
    c text COLLATE pg_catalog."default" NOT NULL,
      _id_a_id bigint,
    CONSTRAINT b_pkey PRIMARY KEY (id),
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT fk_a_id_a FOREIGN KEY (_id_a_id)
        REFERENCES public.a (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

They are correctly binded with Wt::Dbo.
And i want to make that query:

select * from b as tb join a ta on tb._id_a_id=ta.id where ta.enable=true and tb.c='jjjjj';

So i wrote that Wt line:

  Wt::Dbo::ptr<B> listB=session->find<B>("as tb").join("a ta on tb.id = ta.id").where("lb.c=?").bind("some string var").where("ta.enable=true");

And it gives me the error "ERROR: column reference "id" is ambiguous"
I think that the problem is on the select portion of the query, i should insert the parameters like "ta.i, tb.id, tb.c" but i don't know ho to do it.

Could you please help me?


Replies (1)

RE: Wt::Dbo join - - Added by Matthias Van Ceulebroeck 11 months ago

Hello,

my apologies for the late reply.

The Wt::Dbo::Session::find() method is to be used if you are only retrieving results from a single table. For more complex queries it is better to use Wt::Dbo::Session::query().

You can for example create a query that looks like this:

using Result = std::tuple<long long, long long, std::string>;
Wt::Dbo::collection<Result> result = session->query<Result>("SELECT ta.id, tb.id, tb.c FROM b tb").join("a ta ON tb._id_a_id = ta.id").where("tb.c = ?").bind("some string var").where("ta.enable = ?").bind(true);

Otherwise if you use find() where there are columns that have identical names, it will find "duplicates", calling the column ambiguous. This is because find does not add the alias to its result. That means it generated "SELECT id AS col0, version AS col1, ...".
Only when you use query() can the alias be added to the SELECT clause.

If you do want to use the Wt::Dbo::ptr here, you can add that to the tuple like:

using Result = std::tuple<Wt::Dbo::ptr<B>, long long, std::string>;
Wt::Dbo::collection<Result> result = session->query<Result>("SELECT tb, tb.id, tb.c FROM b tb").join("a ta ON tb._id_a_id = ta.id").where("tb.c = ?").bind("some string var").where("ta.enable = ?").bind(true);

You can even return both objects by using:

using Result = std::tuple<Wt::Dbo::ptr<B>, Wt::Dbo::ptr<A>>;
Wt::Dbo::collection<Result> result = session->query<Result>("SELECT tb, ta FROM b tb")...

I hope this sufficiently resolves your issue.

    (1-1/1)