Correctness of query: join omitted in output when executing query.

[germandiagogomez]

Hello everyone,

Thanks for this great project. Have a quick question.

Problem

Maybe it is me (likely) but I fail to see why for this query the output omits the join when generating the code to be executed... Joins are not generated.

auto query =
      sqlpp::select(orsInvitationsTab.invitationId, orsInvitationsTab.recipe,
                   orsInvitationsTab.sender, orsInvitationsTab.kind)
          .from(orsInvitationsTab.left_outer_join(orsInvitationsRepliesTab).
                on(orsInvitationsRepliesTab.invitationId != orsInvitationsTab.invitationId ||
                    orsInvitationsRepliesTab.recipe != orsInvitationsTab.recipe))
          .where(orsInvitationsTab.recipe == userId_);

generates this:

SELECT online_rooms_invitations.invitation_id,online_rooms_invitations.sent_date,online_rooms_invitations.sender,online_rooms_invitations.recipe,online_rooms_invitations.kind FROM online_rooms_invitations WHERE (online_rooms_invitations.recipe='myuserid')

Tables

CREATE TABLE IF NOT EXISTS online_rooms_invitations (
       invitation_id CHAR(36) NOT NULL,
       sent_date DATETIME NOT NULL,
       sender VARCHAR(80) NOT NULL,
       recipe VARCHAR(80) NOT NULL,
       kind SMALLINT NOT NULL,
       PRIMARY KEY (invitation_id, recipe),
       FOREIGN KEY (recipe) REFERENCES online_rooms_users(email),
       FOREIGN KEY (sender) REFERENCES online_rooms_users(email)
);


CREATE TABLE IF NOT EXISTS online_rooms_invitations_replies (
       invitation_reply_id CHAR(36) NOT NULL PRIMARY KEY,
       invitation_id CHAR(36) NOT NULL,
       recipe VARCHAR(80) NOT NULL,
       sent_date DATETIME NOT NULL,
       action SMALLINT NOT NULL,
       FOREIGN KEY (invitation_id, recipe) REFERENCES online_rooms_invitations(invitation_id, recipe)
);

[rbock]

Hi German,

Thanks for the flowers and thanks for the report! That sounds scary.

However, I can't reproduce the issue? I added a smaller example, see 561cc7e.

I also tried your code and received the following (line breaks added for readability):

SELECT 
online_rooms_invitations.invitation_id,online_rooms_invitations.recipe,online_rooms_invitations.sender,online_rooms_invitations.kind 
FROM online_rooms_invitations LEFT OUTER JOIN online_rooms_invitations_replies ON ((online_rooms_invitations_replies.invitation_id<>online_rooms_invitations.invitation_id) OR (online_rooms_invitations_replies.recipe<>online_rooms_invitations.recipe)) 
WHERE (online_rooms_invitations.recipe='cheese_cake'

That's different from what you observed? I wonder what is going on? Is it possible you are linking to an older version of your code?

Cheers,
Roland

[germandiagogomez]

What was actually happening is that the query printed was another. I fixed it.

However, I would like (see above first question) to return the rows from the left table that do not exist in the right table. Note that the key is composite (invitationId + recipe is the primary key in left table and invitationid + recipe is a foreign key in the right table but those cannot be null when a row exists).

I cannot use is_null()/is_not_null() bc the colums from the right table cannot be null... I am not sure how to do it actually with sqlpp11 but I pasted the SQL query below for reference and some context.

--- This is left table
CREATE TABLE IF NOT EXISTS online_rooms_invitations (
       invitation_id CHAR(36) NOT NULL,
       sent_date DATETIME NOT NULL,
       sender VARCHAR(80) NOT NULL,
       recipe VARCHAR(80) NOT NULL,
       kind SMALLINT NOT NULL,
       PRIMARY KEY (invitation_id, recipe),
       FOREIGN KEY (recipe) REFERENCES online_rooms_users(email),
       FOREIGN KEY (sender) REFERENCES online_rooms_users(email)
);

-- This is right table, note the NOT NULL in recipe and invitation_id
CREATE TABLE IF NOT EXISTS online_rooms_invitations_replies (
       invitation_reply_id CHAR(36) NOT NULL PRIMARY KEY,
       invitation_id CHAR(36) NOT NULL,
       recipe VARCHAR(80) NOT NULL,
       sent_date DATETIME NOT NULL,
       action SMALLINT NOT NULL,
       FOREIGN KEY (invitation_id, recipe) REFERENCES online_rooms_invitations(invitation_id, recipe)
);

However, I need to check this in raw sql, basically, this, and the IS NOT NULL condition I am not sure how it can be emitted from sqlpp11:

SELECT online_rooms_invitations.invitation_id,online_rooms_invitations.recipe,online_rooms_invitations.sender,online_rooms_invitations.kind 
FROM online_rooms_invitations 
LEFT OUTER JOIN online_rooms_invitations_replies ON ((online_rooms_invitations_replies.invitation_id<>online_rooms_invitations.invitation_id) OR (online_rooms_invitations_replies.recipe<>online_rooms_invitations.recipe)) 
WHERE (online_rooms_invitations.recipe='germandiago@gmail.com' AND 

-- Problem here, I cannot do .is_not_null() from sqlpp11, it seems.
online_rooms_invitations_replies.invitation_id IS NOT NULL);

[rbock]

Thanks for the confirmation.

As for the follow-up: There is an exists function which takes a select as its argument. You can use it like this:

  auto query =
      sqlpp::select(orsInvitationsTab.invitationId, orsInvitationsTab.recipe, orsInvitationsTab.sender,
                    orsInvitationsTab.kind)
          .from(orsInvitationsTab)
          .where(orsInvitationsTab.recipe == userId_ and
                 not exists(select(orsInvitationsRepliesTab.invitationId)
                                .from(orsInvitationsRepliesTab)
                                .where(orsInvitationsRepliesTab.invitationId == orsInvitationsTab.invitationId and
                                       orsInvitationsRepliesTab.recipe == orsInvitationsTab.recipe)));

Hope this helps?

Cheers,
Roland

[rbock]

Whoops, sorry, had the join still in there. Edited...

[germandiagogomez]

It is solved now thanks.

I had several issues. One was the query itself but also that is_null was not incouded and would not compile (undefined) and lead me to think it was bc the row cannot be null but it was bc of the missing include.

The left join was correct indeed.

Thanks for your fast replies. Closing.