Fetch and order selected data from multiple tables in Codeigniter

I’m struggling around the following problem: into my database there are two table, with the following same structure

Both tables have data about images, one for civil aircraft registrations, the other one for military registrations.

I need to select latest 100 uploaded images from both tables. Additional, I need to join the id from the above table with other tables to retrieve registration code.

I’m using Codeigniter rel. 3 and below there is the code I wrote using Active Records.

$this->regs_db->select (" 
    $this->table_images_civil.imgid,
    $this->table_images_civil.id,
    $this->table_images_civil.image,
    $this->table_images_civil.status,
    $this->table_images_civil.uploadtime,
    $this->table_images_military.imgid,
    $this->table_images_military.id,
    $this->table_images_military.image,
    $this->table_images_military.status,
    $this->table_images_military.uploadtime,
    $this->table_registrations.registration,
    $this->table_military.mm      
    ");

$this->regs_db->from("$this->table_images_civil, $this->table_images_military");
$this->regs_db->join("$this->table_register", "$this->table_images_civil.id = $this->table_register.id","left");
$this->regs_db->join("$this->table_registrations", "$this->table_register.rid = $this->table_registrations.rid","left");
$this->regs_db->join("$this->table_military", "$this->table_images_military.id = $this->table_military.id","left");
$this->regs_db->limit($limit, $start);
$this->regs_db->order_by("$this->table_images_civil.uploadtime", "asc");
$this->regs_db->order_by("$this->table_images_military.uploadtime", "asc");
$this->regs_db->order_by($col, $dir);

$query = $this->regs_db->get();

if($query->num_rows() > 0)
{
    return $query->result(); 
}
else
{
    return null;
}

The code doesn’t work properly….if I get rid of the “left” parameter in joined table I get, after several seconds of wait, result from a huge numbers of data…way more then actual number of images in database. Using “left” parameter in join table the code doesn’t work…and I get a 503 server error.

Any hint to fix this issue? Thanks a lot for any feedback.

Answer

I’m not familiar with code igniter, but I am with SQL.

I would suggest breaking this into what would amount to 3 queries. 2 inside queries, one for each table, and another surrounding those two queries. Think of it like this: select the most recent 100 rows from each table, then union them, and select the most recent 100 rows from that union.

SELECT 
    `Source`,
    `imgid`,
    `id`,
    `image`,
    `status`,
    `uploadtime`
FROM
    (
        SELECT
            '1' AS `Source`,
            `imgid`,
            `id`,
            `image`,
            `status`,
            `uploadtime`
        FROM `table_images_civil`
        ORDER BY `uploadtime` ASC
        LIMIT 100
    UNION
        SELECT
            '2' AS `Source`,
            `imgid`,
            `id`,
            `image`,
            `status`,
            `uploadtime`
        FROM `table_images_military`
        ORDER BY `uploadtime` ASC
        LIMIT 100
    )
ORDER BY `uploadtime` ASC
LIMIT 100

The column source is there just in case you need to know from which table the data originated.

Note that the inner selects both get 100 rows. That’s because if either military or civilian had all of the top 100, then that limit would provide enough rows for the outer select without adding more overhead on the server for the outermost query.

Not sure how this would be coded in code igniter, but you should be able to figure that part out. Hopefully, the SQL statement is helpful, and conveys the idea in an easy to understand form.