[SOLVED] Convert mariaDB query to ColdBox Criteria

Convert mariaDB query to ColdBox Criteria

i'm having a problem in converting a mariaDB query to coldbox criteriaBuilder. i not quite understand how to join multi table using criteriaBuilder in coldbox. this is my mariaDB query :

select TerminalInst.* from TerminalInst 
inner join Terminal on TerminalInst.terminal_id = Terminal.id
inner join custinst on TerminalInst.custinst_id = custinst.instID
where Terminal.description like '%value%'

how to convert this query to criteriaBuilder? please help me..

Solution

Try something like this (within a handler):

property name="terminalInstService" inject="model:terminalInstService"; //for cbox 3.8.x
property name="terminalInstService" inject="model"; // for cbox 4.2.x

cr = terminalInstService.newCriteria();  
cr.createAlias('terminals','t')
   .createAlias('custinsts','c')
   .ilike('t.description','%value%');

results = cr.list();

where

terminals = relationship name between TerminalInst and Terminal
custinsts = relationship name between TerminalInst and custinst
and ilike is for case in-sensitive like