Groups can be a useful way to allow the user to build conditions to limit the records to perform some action on. Recently, we designed a job that would work on a set of records and we allow the users to define the list of records by adding conditions to a group. The job would get the SQL from the group and then perform some work on them. Of course, all of this depends on the job being able to get the records from the defined group to perform the work. Luckily, getting the SQL for a group turned out to be a pretty task.
Getting the SQL for a Group
To get the group’s SQL, you’ll need the groups’ plugin ID value. Then, you’ll get the values from a GroupInfo object and piece them together as needed.
The following code will do just this:
// first, get the groupInfo for the group
var groupInfo = new Sage.SalesLogix.Client.GroupBuilder.GroupInfo
{
UseAliases = true,
GroupID = "pMKJ2A100002" // the group's plugin ID value
};
// construct the SQL statement
var commandText = "SELECT * FROM " + groupInfo.FromSQL;
if (!string.IsNullOrEmpty(groupInfo.WhereSQL)) commandText += " WHERE " + groupInfo.WhereSQL;
// now run the SQL to get the records in the group
var dataService = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Data.IDataService>();
using (var conn = new System.Data.OleDb.OleDbConnection(dataService.GetConnectionString()))
{
using (var command = new System.Data.OleDb.OleDbCommand(commandText, conn))
{
// we need to set any parameter values from the group in our SQL command
groupInfo.AddCommandParams(groupInfo.LoadGroupXmlDocument(), command);
conn.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
// now do whatever we need with the records in the group
idList.Add(reader[0].ToString());
}
}
}
}
That’s it. Note, in my case, I really don’t care about the “SELECT” fields in the group layout or how the group is sorted. I just want to know what records are in the group, so I am only using the tables from the group in the SQL’s FROM clause and the conditions for the group in the SQL’s WHERE clause. Now, my job can simply run the SQL statement to get the records in the group and do whatever work it needs to do on them.
Hi, Nice post, but why did you use the sql to get the Ids.
You could also use the GetGroupIDs Method.
You then can use the GetById Method to get the full NHibernate object.
I know that sql is sometimes faster, but if you have rules in your system, then they are not fired if you use hql or sql statements.
Hi Alexander,
For our case, we created a job that did some processing on records. We created a UI for the users to choose groups that would determine the records the job work perform work on. We also needed to know which records already had been processed by a previous run, so we needed to append SQL conditions to the query. Getting the group ID values and then check each one would have been far too inefficient. Using the method above, we were able to easily append additional conditions to the SQL where clause.
However, just to point out, getting the group ID values also does use a native SQL query. It does not use nhibernate to retrieve the ID values either which can be seen in Sage.SalesLogix.Client.GroupBuilder.GroupInfo.GetGroupIDs using reflector.
Either way, when we are talking about reading records, such as in the case of reading the records that are in a group, it does not matter whether it goes through the entity model/nhibernate or direct SQL since the entity events only apply to actions performed on those records (inserts/updates/deletes). The code above it only used for reading the records, for performing any of these actions using the entity model is the way to go to ensure entity rules are enforced.
Ryan
OK, you are right. Just wanted to point out that if you onyl want to get the IDs from a group and not want to append conditions it is less code to use GetGroupIds.
But yes, in your case appending the where clause is better.
Sorry for this odd post… We recently changed a few fields in the ACCOUNT table and turned them into computed columns. After testing and finding that it did not work, I’ve changed the columns back to their original datatype but now data in these fields are not showing up in TICKET groups.
For example, Account status is showing blank in the group, but there is underlying data there.
Any thoughts on how to correct? This is SLX 7.5 🙁