user table join another table

Sonia Samimi asked on February 4, 2020 07:12

Hi there; how to get Kentico users via a join with another table? I have a custom field in the CMS-User table and it is a foreign key of another table which I named it BusinessUnit, I want to get users via joining the user's table with the BusinessUnit table. could you please help me to do that?!!

Correct Answer

Dat Nguyen answered on February 4, 2020 09:14

If BusinessUnit is a custom module and assuming that the foreign key in the Users table and the primary key in the BusinessUnit table are both named BusinessUnitID, this snippet should return a dataset of the inner join of the two tables:

var dataSet = UserInfoProvider.GetUsers()
    .Source(source => source.Join<BusinessUnitInfo>("BusinessUnitID", "BusinessUnitID"))
    .Result; //dataset with properties of both tables, take this out if you want just the collection of UserInfo

If BusinessUnit is instead a custom table, then you can use ConnectionHelper to execute a SQL query, like so:

string selectStatement = 
    "select u.*, b.* from CMS_User u inner join BusinessUnit b on u.BusinessUnitID = b.BusinessUnitID";
QueryDataParameters parameters = null;
var dataSet = ConnectionHelper.ExecuteQuery(selectStatement, parameters, QueryTypeEnum.SQLQuery);

Obviously, modify the select statement and parameters as needed. Refer to this documentation to see the overloads of the ConnectionHelper.ExecuteQuery() method.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on February 4, 2020 15:16

The easiest thing to do is create a custom query in either your module or a container only page type with your custom "join" query. Then in your code call that query like so:

QueryDataParameters parms = null;
var ds = ConnectionHelper.ExecuteQuery("your.namespace.querycodename", parms, QueryTypeEnum.SQLQuery);

Taking this approach allows you to leave your code as is even if you need to change the query. You can also take advantage of the parameters in the query so you can use your query in multiple places. The nice part is you can change or tweak the query within the Kentico UI without needing to adjust code.

1 votesVote for this answer Mark as a Correct answer

Sonia Samimi answered on February 5, 2020 06:40

thank you :) it does work LIKE

0 votesVote for this answer Mark as a Correct answer

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