Left join in SalesLogix HqlDataSource
Posted By: nicocrm on May 29th, 2012 in Saleslogix
No Gravatar

HqlDataSource is a very useful component when there is a need to customize the query used to populate a grid and you don’t want to go all the way down to a custom smartpart. It is not terribly intuitive to use though and has a few undocumented quirks. One of those is the way that joins are formed. Say you have the following 2 entities:

  • AccountProduct, with a 1-M link from Account to AccountProduct
  • Contact, with an optional M-1 link from AccountProduct to Contact

On the account level you may want to show all assets (AccountProduct records) associated with the account, with the associated contact if there is one (and a complex condition that makes it impossible to just use the 1-M link from Account to AccountProduct to populate the grid – for example, you may want to only display products of a certain family). Intuitively you would use the following columns in your HqlDataSource:

  • Product.Name
  • Contact.LastName

With a where clause such as: Account.Id=:AccountID and Product.Family = 'Nice'

Unfortunately this will cause a query with an inner join from AccountProduct to be sent, something like select c.lastname, p.name from accountproduct ap inner join product p on p.productid=ap.productid inner join contact c on c.contactid=ap.contactid ... – which will of course exclude any accountproduct record that does not have a contactid

The way around it is to play with the “From” property of the HqlDataSource control – this is the type of joins used in the query can be specified. It does require you to specify an alias for every entity used in the query, though! So for the example above, use a “From” clause of “AccountProduct as ap left join ap.Contact as c“, and use the field definitions in the columns:

  • ap.Product.Name (note how you have to now include the “ap” alias, instead of just the property name)
  • c.LastName

Often times the answer to HqlDataSource limitations can be found in the standard HQL documentation, the trick is then to figure out the right combination to get the control to output the correct HQL.

Leave a Reply