Handling joins in object query

Revolution Golf asked on September 6, 2016 18:14

My question comes in two parts. For starters and I will tell you what I first attempted and how that lead to a workaround with a dead end. Hopefully you can tell me how I could of accomplished this without the work around or how I can get the workaround to work properly.

The original goal was to perform a left join between a regular Kentico table and a custom table. The query would of looked something like this:

select
    table.*
from table
left join customtable on customtable.id = table.id
where customtable.id is null

When I tried this using an ObjectQuery I ran in to difficulty using a custom table with the Source method. Instead of fighting this very long I just reversed the query like this:

select
    table.*
from customtable
right join table on table.id = customtable.id
where customtable.id is null

I was able to get this working with the object query except for the data being selected. When I try and get the results of the query the object query seems to have the limitation of only returning the object type of the starting table...in this case CustomTableItem. I will continue to play with this as I wait for posted answers but I thought someone out there might have already run across this and will have an answer quicker than I will.

Thank you, Brandon

PS - Anyone who is going to answer by suggesting an alternative like custom queries please do not respond. I am only wanting to learn more about the ObjectQuery. Also...after posting the question I found the GetListResult which does allow me to return a different type than the original table. I'd still love to hear other thoughts....especially on still being able to do the left join.

Recent Answers


Richard Sustek answered on September 6, 2016 20:36

Your question is quite oddly formatted.. Not sure if that is on purpose, but it is really hard to read.

Anyway, if you want to learn more about ObjectQuery I think the best thing you can do is to check following 2 articles which contain a lot of working examples of what you can do with ObjectQuery and how. It will be much more efficient then trying to explain it here.

Here you go:

http://devnet.kentico.com/articles/kentico-8-technology-dataquery-api

http://devnet.kentico.com/articles/kentico-8-technology-dataquery-advanced-api

0 votesVote for this answer Mark as a Correct answer

Revolution Golf answered on September 6, 2016 20:41

Richard, I appreciate the attempt but I have read those articles many times. If you were to read them again with my question in mind you would find that it does not touch on doing a join with a custom table. Unfortunately your answer doesn't seem to apply unless I am missing something. You will need to point out exactly what part of those articles you are thinking apply otherwise I'll have to wait for others to answer.

 Thank you,
    Brandon
0 votesVote for this answer Mark as a Correct answer

Revolution Golf answered on September 6, 2016 20:42

As far as the formatting of my question that is what the Kentico Q&A board is doing by default. Not sure why it decided to do that

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on September 6, 2016 21:16

How about this:

var joinQuery = UserInfoProvider.GetUsers()
    .Source(sourceItem => sourceItem.Join<UserSettingsInfo>("UserID", "UserSettingsUserID"))
    .WhereEmpty("UserPassword");
1 votesVote for this answer Mark as a Correct answer

Revolution Golf answered on September 6, 2016 21:25

All, Please remember that when you answer this question I am asking in reference to custom tables...the above answer doesn't refer to custom tables at all. If custom tables are not involved in your answer then your answer is not valid. Thank you, Brandon

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on September 6, 2016 22:09

Brandon, if you create a class for your custom table on the code view for the custom table(inside of the kentico admin portal), that may help you out. Then maybe you can use that class to cast your custom table to an object. This could possibly make the above item work, but in any case you could possibly use something like the example on the following link. There are a couple of items in the article that may help you out. Note, none of them EXPLICITLY use custom tables, but having some knowledge of database tables and queries may help you use it.

http://devnet.kentico.com/articles/kentico-8-technology-dataquery-advanced-api

0 votesVote for this answer Mark as a Correct answer

Zachary Perry answered on September 6, 2016 22:23 (last edited on September 6, 2016 22:33)

I believe you want to use Data Query instead of object query:

new DataQuery().From(new QuerySource("CMS_Users")
                      .LeftJoin("CustomTable_TableName", "CMS_Users.UserID",
                          "CustomTable_TableName.UserID")).Where("CustomTable_TableName.UserId", QueryUnaryOperator.IsNotNull);

Which is mentioned here.

0 votesVote for this answer Mark as a Correct answer

Martin Hejtmanek answered on September 8, 2016 12:28

Hi, when using joins, you can always start with the most left source the way you would retrieve it normally. In this case your source is a custom table so you just query custom table items for that table.

var query = 
    CustomTableItemProvider.GetItems("custom.yourtable");

Then you just extend this with the join using the Source method:

var query =
    CustomTableItemProvider.GetItems("custom.yourtable")
        .Source(s => s
            .LeftJoin(
                "Other_Table",
                "ItemID",
                "OtherID",
                new WhereCondition()
                    .WhereNotNull("OtherTable.OtherID")
            )
        );

Left remains current, table is added as right, and if not fully qualified, column names for the ON condition are prefix with latest left and the new right tables. Optionally you can add condition to JOIN, or to the query itself.

If the left most table does not have native Kentico API, you can always use low-level DataQuery.

This really mostly is information from those two articles plus documentation for custom tables (https://docs.kentico.com/display/API9/Custom+table+data), you just sometimes need to decompose individual pieces of the information, and put some of them together the right way. I am sure you will learn this as you work more with it. In general it is a good practice to make an automated test, or some empty page, and just try various combinations and observe how they work together.

Hope this helped.

2 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.