2

How can I transform this SQL to CodeIgniter query? I have screenshot of an EER diagram to help you understand better.

There are three tables to join. I guess, one is "pjesma", then "izvodjac", and "izvodi_pjesmu".

This SQL works when I run it and gives me results I want. I am also using pagination so I need limit and offset somehow included.

EER diagram

SELECT pjesma_id, naslov, naziv
FROM pjesma p, izvodjac i, izvodi_pjesmu ip
WHERE p.pjesma_id = ip.pjesma_pjesma_id
AND i.izvodjac_id = ip.izvodjac_izvodjac_id

in model:

public function paginacija_pjesme($limit, $offset) {

    $this->db->select('pjesma_id', 'naslov', 'naziv');
    $this->db->from('pjesma p');
    $this->db->join('izvodi_pjesmu ip', 'p.pjesma_id=ip.pjesma_pjesma_id');
    $this->db->join('izvodjac i', 'i.izvodjac_id=ip.izvodjac_izvodjac_id');
    $this->db->limit($limit, $offset);
    $query = $this->db->get();
    
    return $query->result();
}

EDIT: So in select(), I used the wrong syntax, this line:

$this->db->select('pjesma_id', 'naslov', 'naziv');

should be like this:

$this->db->select('pjesma_id, naslov, naziv');
Improve this question
0

1 Answer 1

Reset to default
1

A few things, as you are dealing with objects within the query builder class, you can method-chain and make things a little less cluttered.

You are maybe getting an ambiguous column error, joining and not prefixing your select. Hard to tell without the error being posted you are facing.

Always best to (just in case) set defaults to your method arguments as well, in case you pass a blank variable (or don't need to for whatever reason).

When you are using multiple tables in the FROM statement you are actually doing a CROSS JOIN, and your new code is giving INNER JOIN,trying the join syntax direct to your SQL server might help as you are not actually doing the same thing between your first written query, and the codeigniter one.. Try the below with LEFT join just to see... If there are NULL joins using INNER they will be omitted

public function paginacija_pjesme($limit = 10, $offset = 0) {

    $query = $this->db->select('p.pjesma_id, p.naslov, i.naziv')
        ->from('pjesma p')
        ->join('izvodi_pjesmu ip', 'p.pjesma_id = ip.pjesma_pjesma_id', 'left')
        ->join('izvodjac i', 'i.izvodjac_id = ip.izvodjac_izvodjac_id', 'left')
        ->limit($limit, $offset)
        ->get();

    return $query->result();
}

Are you getting any errors? what do you see if you print_r($query->result()) ?

try also spitting out $this->db->last_query() - this will output the SQL codeigniter has build. Then running this directly to your database, and see the results from there. You might even see the issue without needing to run it.

This is an example of what your current codeigniter code is generating (as you can see, different to your original query):

SELECT pjesma_id, naslov, naziv
FROM pjesma p
INNER JOIN izvodi_pjesmu ip ON p.pjesma_id = ip.pjesma_pjesma_id
INNER JOIN izvodjac i ON i.izvodjac_id = ip.izvodjac_izvodjac_id
# LIMIT 10,0
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

SELECT p.pjesma_id FROM pjesma p LEFT JOIN izvodi_pjesmu ip ON p.pjesma_id=ip.pjesma_pjesma_id LEFT JOIN izvodjac i ON i.izvodjac_id=ip.izvodjac_izvodjac_id LIMIT 5 -this is output from last_query()
when i run this query you wrote (with INNER JOIN) in workbench it works as i want
Then the issue is with how you're processing the result
in view i try to display "naslov" and "naziv" and i get undefined property error, thank you again for your help, query needs to be modified i guess let me try
How are you handling it after the method returns the result object?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.