left join source

crush123

i have an editor instance with a left join

what i am doing is counting the number of times a certain id occurs in the joined table

on my datatable i have a delete button which is rendered on condition that the left joined source returns null, so that delete is only possible in the main table, if there are no dependent rows.

The only way i have been able to get this to work is to create a view returning a count of the id, and using the view in the join.

This works fine, but it means creating a view each time I want to do this.

eg

->leftJoin( 'vw_itemtypes_count', 'vw_itemtypes_count.ItemTypeID', '=', 'refitemtype.ItemTypeID' )

Is it possible to have my left join use a standard mysql expression so i could create the join without having to create the view each time ?

allan

What SQL expression would it be that you would use to do this?

However, Editor's understanding of SQL is very limited - it needs to "know" what each element of the query is, and how it should be processed - which is why it has such a limited sub-set of SQL available via the API.

Thanks,
Allan

crush123

That's cool.

I'm pleased that it works, I just had the thought that it would be nice if I could replace the view (eg 'vw_itemtypes_count', which contains an aggregate function), with something like...

$sql = $db->sql("SELECT tblitem.ItemTypeID AS ItemSizeID,count(tblitem.ItemTypeID) AS CountOfItems from tblitem group by tblitem.ItemTypeID;)

and use that in the left join instead.

allan

You can certainly execute raw SQL like that. At the moment Editor doesn't accept functions (etc) in its field list, although I hope to add that future in future.

Allan