How do you do left joins?

[Robert Campbell]

I want to see all records from the cmdb_ci table that have u_core_ci = true and sys_class_name = cmdb_ci_service OR u_cmdb_ci_business_application.  I want to see where those records show up on the cmdb_rel_ci table as a child where the parent is of the class cmdb_ci_business_app.  I want to use this to get a report that will show data from both the cmdb_ci table and cmdb_ci_business_app table.  My MySQL query gives the correct results.  I can't tell if the Background Script is giving the correct results because I can't get it to show the value from the cmdb_ci_business_app table or the parent from the cmdb_rel_ci table.  The view I created times out.

MySQL:

select 
  c1.name
  ,r1.parent 
from 
  cmdb_ci c1 
left join 
  cmdb_rel_ci r1 
on 
  r1.child = c1.name 
left join 
  cmdb_ci_business_app b1 
on 
  b1.name = r1.parent 
where 
  c1.u_core_ci = 'true' 
  and c1.sys_class_name='Business Service' 
    or c1.sys_class_name='Business Application';

If I export the tables and run this, it works correctly.

Background Script:

var c1 = new GlideRecord('cmdb_ci');
c1.addQuery('sys_class_name','cmdb_ci_service').addOrCondition('sys_class_name','u_cmdb_ci_business_application');

var r1 = c1.addJoinQuery('cmdb_rel_ci','sys_id','child');
var b1 = r1.addJoinQuery('cmdb_ci_business_app','parent','sys_id');

c1.query();

while(c1.next()) {
gs.print(c1.name+ ' ' +c1.sys_class_name+ ' ' +b1.name);
}

gs.print(c1.getRowCount());

Database View

Name: u_business_application_and_business_service

Table	Active	Left Join	Order	Variable prefix	Where clause
cmdb_ci	true	true	100	c1	(c1.sys_class_name='cmdb_ci_service' || c1.sys_class_name='u_cmdb_ci_business_application') && c1.u_core_ci=true
cmb_rel_ci	true	true	200	r1	r1.child=c1.sys_id
cmdb_ci_business_app	true	true	200	b1	b1.sys_id=r1.parent

I've tried all combinations of left join = true with no success.  I would think only cmdb_rel_ci would be left join true but that doesn't work

[-O-]

When using dot notation in table-field tokens one needs to use "real" DB field names (when the query translation logic detects the 1st dot in a table-field token, the translation stops and everything after it is just copied into the final query as is).

I'm 99.99% sure that at least u_core_ci's real table name is not u_core_ci, but something like a_str_<some number>.

That is most likely one of the reasons why the view fails - there may be others.

The better solution is to use underscored table-field token notation (e.g. b1_sys_id) - the standard, supported way.

JFYI: One can also use dot notation in table-field tokens, even write plain MySQL where clauses in that case (with the full power of DB functions), but in that case one needs to look up table field aliases in sys_storage_alias and use the "real" field names in the where clause. E.g. b1.u_str_<some number>.

But it may not be that portable as it can happen that the same field has a different alias in a different instance.

Even table names can be aliased, but that seldom happens.

When it happens it is most likely with tables involved in the CMDB hierarchy - even OOB ones.

You will find this information in the same table.

But, as said above, it is better to stick with the OOB, supported solution.

[Robert Campbell]

In the database view, if I take out all of the left joins, the view works and I've always used table_reference.column, mainly because that is how I was taught back on London but I can use table-reference_column.  The problem seems to be with the left join function specifically because it does work w/o the left join.

[Brad Bowman]

Hi Robert,

From your description, I'm not seeing where a left join is needed since you only want to see CIs that are related with the child from the c1 table and the parent from b1, but you can add one in somewhere if there are records missing.  Beyond that, I think this is a case of expecting to do too much in the Database View Where clause.  Start with a basic view like this:

Once you confirm that this view at least includes the correct records - and some that you don't want to see - you can move on to the next step.  Create a before Query Business Rule using the Database View as the Table.  Your Script on the Advanced tab will look like this:

(function executeRule(current, previous /*null when async*/) {
	current.addEncodedQuery('c1_sys_class_name=cmdb_ci_service^ORc1_sys_class_name=u_cmdb_ci_business_application^c1_u_core_ci=true');
})(current, previous);

[Robert Campbell]

I need the left join because I want to see all cis that meet the criteria regardless of its relationship.   

1. I want to see all the cis in the class of cmdb_ci_service, cmdb_ci_business_app, u_cmdb_ci_business_application.
2. I also want to see where the above cis are mapped to cmdb_ci_business_app or u_cmdb_ci_business_application

This works but not what I want.  This only shows where the requested cis are on the relationship table.

1. Show cis that are in the classes and is core ci = true. (736)
2. Show only the above where its on the relationships table as a child. (1343)
3. Show all the business applications that are a parent on the relationship table. (474)
4. Total (399)