Builder query question From table AS table01, table AS table02

Builder query question From table AS table01, table AS table02 - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Builder query question From table AS table01, table AS table02 (/showthread.php?tid=76070)

Builder query question From table AS table01, table AS table02 - Bart Goossens - 04-12-2020

I would like to make a Builder Query base on

PHP Code:
$tQRY = "SELECT node.*
         FROM ".$this->dbTable." AS node,
              ".$this->dbTable." AS parent
         WHERE node.LFT BETWEEN parent.LFT AND parent.RGT
              AND parent.NAME = '".$node."'
         ORDER BY node.LFT";
$query = $this->db->query($tQRY);
$this->dbLastQuery =  $this->db->getLastQuery(); 

Result of this query :

Code:
SELECT node.* 

FROM da_mainmenu AS node, da_mainmenu AS parent 

WHERE node.LFT BETWEEN parent.LFT AND parent.RGT AND parent.NAME = 'Elektriciteit' 

ORDER BY node.LFT

When is transform it to a full builder

PHP Code:
$builder = $this->db->table($this->dbTable);
$builder->resetQuery();
$builder->select('node.*');
$builder->from($this->dbTable .' AS node');
$builder->from($this->dbTable .' AS parent');
$builder->where('node.LFT BETWEEN ', 'parent.LFT AND parent.RGT');
$builder->where('parent.NAME ', $node);
$builder->orderBy('node.LFT');
$query =  $builder->get(); 

with following result

Code:
SELECT node.* 

FROM da_mainmenu, da_mainmenu AS node, da_mainmenu AS parent 

WHERE node.LFT BETWEEN 'parent.LFT AND parent.RGT' AND parent.NAME = 'Elektriciteit' 

ORDER BY node.LFT

I get the first da_mainmenu to much.
So there is problem with

PHP Code:
$builder->from($this->dbTable .' AS node');
$builder->from($this->dbTable .' AS parent'); 

How can i transform the query to builder?
Also I'm not sure how to do the 'WHERE X BETWEEN y AND Z'
** solved **

Class is included

RE: Builder query question From table AS table01, table AS table02 - maxxd - 04-12-2020

Just code the where clause directly, like so:

Code:
$builder->where('node.LFT BETWEEN parent.LFT AND parent.RGT');

There's no variable interpolation or substitution so it should be fine.

RE: Builder query question From table AS table01, table AS table02 - Bart Goossens - 04-12-2020

(04-12-2020, 01:21 PM)maxxd Wrote: Just code the where clause directly, like so:

Code:
$builder->where('node.LFT BETWEEN parent.LFT AND parent.RGT');

There's no variable interpolation or substitution so it should be fine.

Thanks

Also problems solved with the table selection

PHP Code:
$this->db = db_connect($this->dbActiveGroup);

$builder = $this->db->table("$this->dbTable AS da_node,  $this->dbTable AS da_parent");
$builder->resetQuery();

$builder->select("da_parent.*");
$builder->where("da_node.LFT BETWEEN da_parent.LFT AND da_parent.RGT");
$builder->where("da_node.NAME ", $node);

$builder->orderBy("da_parent.LFT");

$query =  $builder->get();
$this->dbLastQuery =  $this->db->getLastQuery();
return $query; 

RE: Builder query question From table AS table01, table AS table02 - Bart Goossens - 04-12-2020

Solved