How to select data from joined table

How to select data from joined table - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Model-View-Controller (https://forum.codeigniter.com/forumdisplay.php?fid=10)
+--- Thread: How to select data from joined table (/showthread.php?tid=80145)

How to select data from joined table - sprhld - 09-18-2021

Hi there,
I have a table and some relations in an 2nd one. My model for the first table works fine. But if I need data from this relation-table, it get Unknown column 'r.id' in 'field list'.
In detail, this is how my model is configured:

PHP Code:
    protected $table         = 'epoche';
    protected $primaryKey    = 'id';
    protected $returnType    = 'object';
    protected $allowedFields = ['id', 'slug', 'type', 'name']; 

and this is the method, I use

PHP Code:
    public function getRelationsTo($type, $to, $id = NULL)
    {

        $query = $this->builder()
            ->select('epoche.name, epoche.slug, r.id')
            ->where('epoche.type', $type);

        if ($to == 'below')
        {
            $query->join('epoche_relationship as r', 'r.epoche_id = epoche.id')
                ->where('r.type', $type)
                ->where('r.is_in_epoche_id', $id);
        } 

It works fine without the r.id in select. And as I understand, it is the model, which not allows the r.id-column from the 2nd table.

So, how to select data from joined tables if the model does not allow it?

Thanks

PS: I removed some data, so if there is missing something, this is not the reason of the error :-) My model works fine. But now, I need this ID and changed my model only on this little point.

RE: How to select data from joined table - ikesela - 09-18-2021

it should be no problem unless not meet condition for $to,
let debug it. first you can check the real query generated

$db = db_connect(); or $this ->db (if inside model)

$db->showLastQuery(); or $this->db->getLastQuery();

Try the generate query on external program like phpmyadmin or HeidiSsql to check error

RE: How to select data from joined table - iRedds - 09-18-2021

Move r.id from main query to condition

PHP Code:
if ($to == 'below')
{
    $query->select('r.id')
     -> // etc
} 

RE: How to select data from joined table - sprhld - 09-19-2021

(09-18-2021, 07:04 PM)iRedds Wrote: Move r.id from main query to condition

PHP Code:
if ($to == 'below') { $query->select('r.id') -> // etc }

That's it. I had something like this in mind, but as the whole select. I didn't that because of a clean code (as good as I can *g). That I can add things to the select part is nice.

Thank you!