Hello,

I've been looking at the posts and read about FieldCompareSetPredicate in the docs. I'm still confused as I want to create a subquery on my Join condition. How can I write this using LLBLgen?

SELECT ua.ua_user_lan_id, ua.ua_alert_stage_id, Alert_Stages.as_alert_stage_id, Alert_Stages.as_alert_stage FROM (select * from user_alerts where ua_user_lan_id = 'MurphyC') as ua RIGHT JOIN Alert_Stages ON ua.ua_alert_stage_id = Alert_Stages.as_alert_stage_id

I'm using VB.Net 2005 and Self Servicing.

Thanks, Connor

I think the following query will give the same results as yours:

SELECT  
user_alerts.ua_user_lan_id,
user_alerts.ua_alert_stage_id,
Alert_Stages.as_alert_stage_id,
Alert_Stages.as_alert_stage
FROM        
user_alerts
RIGHT JOIN
Alert_Stages ON ua.ua_alert_stage_id = Alert_Stages.as_alert_stage_id
WHERE user_alerts.ua_user_lan_id = 'MurphyC'

Which can be implemented using a DynamicList with a simple relation and a filter.

Hi Walaa,

Thank you for your response but that query will not produce the same result set. I want to return all rows for "MurphyC"... PLUS all other alert stages that "MurphyC" has NOT subscribed to. Your query just shows the alert stages that MurphyC has subscribed to and does not include the remaining alert stages that MurphyC has not subscribed to.

Let me better explain what I'm hoping to achieve.

Situation There are 10 Alert stages. Our user "MurphyC" has subscribed to 5 of these alerts and this has been recorded in the user alerts tables

Complication The system needs a page to allow users to manage their alerts. We want to display a data grid using useralertscollection which will show all 10 alert stages and a checkbox. The checkbox will be checked if the user has this alert saved in the user_alerts table. The reason I have gone with the right outer join is that it returns a sql result set of exactly what (i.e a row for all 10 alert stages) I want to bind to the data grid.

Question How do I best achieve this in LLBLgen?? I have modifed my query (No major change - I just removed 2 columns as I can get the alertStagename by extending the useralertEntity).

SELECT    ua.ua_user_lan_id,
ua.ua_alert_stage_id
FROM        
(select * from user_alerts where ua_user_lan_id = 'MurphyC') as ua
 RIGHT JOIN
Alert_Stages ON ua.ua_alert_stage_id = Alert_Stages.as_alert_stage_id

Answer Convert the SQL query to work with LLBLGEN??? Can this be done via LLBLgen?? Hopefully you can help ???

My initial failed attempts

            Dim userAlerts As New UserAlertsTypedListTypedList ' UserAlertsCollection()

            Dim relationsToUse As New RelationCollection()
            Dim selectFilter As IPredicateExpression = New PredicateExpression()

            Dim relation As IEntityRelation = UserAlertsEntity.Relations.AlertStagesEntityUsingUsAlertStageId
            relation.HintForJoins = JoinHint.Right
            relation.CustomFilter.Add(selectFilter) ' = (UserAlertsFields.UaUserLanId = "MurphyC")
            relationsToUse.Add(relation)

Thank you, Connor

P.S. There are actually a few instances where I need to do a simalar query within this and other projects. So I appreciate any help you can give.

I came here today looking for the answer to precisely the same thing. I am working with roles and permissions and when a role is selected I want to show in a datagrid which permissions are associated with the role using a checked box and those that aren't associated but could be with an unchecked box. The goal is to give the admin the ability to check and uncheck boxes to assign permissions to a role.

Hopefully you receive an answer because it will help me out as well.

The query of Walaa should be changed to:

SELECT  
user_alerts.ua_user_lan_id,
user_alerts.ua_alert_stage_id,
Alert_Stages.as_alert_stage_id,
Alert_Stages.as_alert_stage
FROM        
user_alerts
RIGHT JOIN
Alert_Stages ON ua.ua_alert_stage_id = Alert_Stages.as_alert_stage_id
WHERE user_alerts.ua_user_lan_id = 'MurphyC' OR user_alerts.ua_user_lan_id IS NULL

I think this query gives you the desired result?

(edit) Darn, too late

Connor wrote:

Hi Walaa,

Thank you for your response but that query will not produce the same result set. I want to return all rows for "MurphyC"... PLUS all other alert stages that "MurphyC" has NOT subscribed to. Your query just shows the alert stages that MurphyC has subscribed to and does not include the remaining alert stages that MurphyC has not subscribed to.

this is the query:

SELECT  
user_alerts.ua_user_lan_id,
user_alerts.ua_alert_stage_id,
Alert_Stages.as_alert_stage_id,
Alert_Stages.as_alert_stage
FROM        
user_alerts
RIGHT JOIN
Alert_Stages ON ua.ua_alert_stage_id = Alert_Stages.as_alert_stage_id
AND
user_alerts.ua_user_lan_id = 'MurphyC'

you can also do:

SELECT  
user_alerts.ua_user_lan_id,
user_alerts.ua_alert_stage_id,
Alert_Stages.as_alert_stage_id,
Alert_Stages.as_alert_stage
FROM        
user_alerts
RIGHT JOIN
Alert_Stages ON ua.ua_alert_stage_id = Alert_Stages.as_alert_stage_id
WHERE
user_alerts.ua_user_lan_id = 'MurphyC'
OR 
user_alerts.ua_user_lan_id IS NULL

LLBLGen Pro doesn't support derive tables (i.e the (SELECT *.. ) statements in a FROM clause), so you've to solve it differently.

Walaa's query would return the right values in the situation of an INNER join, the RIGHT join will give some false positives indeed. So when you use the CustomFilter feature to add the predicate for user_alerts.ua_user_lan_id = 'MurphyC' to the relation, you're set. See for more information about CustomFilter: Using the generated code - Adapter/Selfservicing - Advanced Filter usage -> custom filters for entity relations.

Let me better explain what I'm hoping to achieve.

Situation There are 10 Alert stages. Our user "MurphyC" has subscribed to 5 of these alerts and this has been recorded in the user alerts tables

Complication The system needs a page to allow users to manage their alerts. We want to display a data grid using useralertscollection which will show all 10 alert stages and a checkbox. The checkbox will be checked if the user has this alert saved in the user_alerts table. The reason I have gone with the right outer join is that it returns a sql result set of exactly what (i.e a row for all 10 alert stages) I want to bind to the data grid.

Question How do I best achieve this in LLBLgen?? I have modifed my query (No major change - I just removed 2 columns as I can get the alertStagename by extending the useralertEntity).

SELECT    ua.ua_user_lan_id,
ua.ua_alert_stage_id
FROM        
(select * from user_alerts where ua_user_lan_id = 'MurphyC') as ua
 RIGHT JOIN
Alert_Stages ON ua.ua_alert_stage_id = Alert_Stages.as_alert_stage_id

Answer Convert the SQL query to work with LLBLGEN??? Can this be done via LLBLgen??

Yes it's possible, but not with the derived table statement (the '(select * from user_alerts where ua_user_lan_id = 'MurphyC') as ua'.

Hopefully you can help ???

My initial failed attempts

            Dim userAlerts As New UserAlertsTypedListTypedList ' UserAlertsCollection()

            Dim relationsToUse As New RelationCollection()
            Dim selectFilter As IPredicateExpression = New PredicateExpression()

            Dim relation As IEntityRelation = UserAlertsEntity.Relations.AlertStagesEntityUsingUsAlertStageId
            relation.HintForJoins = JoinHint.Right
            relation.CustomFilter.Add(selectFilter) ' = (UserAlertsFields.UaUserLanId = "MurphyC")
            relationsToUse.Add(relation)

hmm, I'd do: relation.CustomFilter = selectFilter ' = (UserAlertsFields.UaUserLanId = "MurphyC")

but that shouldn't matter much. What query does this piece of code produce? Could you check that with DQE tracing for us please?

tmpcl wrote:

I came here today looking for the answer to precisely the same thing. I am working with roles and permissions and when a role is selected I want to show in a datagrid which permissions are associated with the role using a checked box and those that aren't associated but could be with an unchecked box. The goal is to give the admin the ability to check and uncheck boxes to assign permissions to a role.

Hopefully you receive an answer because it will help me out as well.

This is also in our forum system, you could check the Hnd code if the results just posted aren't helping much in your case: http://www.llblgen.com/Hnd

HnD is this forum system. PLease check the Admin page for Role rights per forum, ManageForumRights.aspx