Multiple joins with ActivRecord

[eluser]Marcelo Wanderley[/eluser]

Hello everybody,

Came a small problem when using joins with activerecord.

Generates an error when I add more than one join.

The codeigniter supports multiple joins?

What could this wrong in my code?


Thank you,


function getEmpresa(){

$this->db->select('*');
$this->db->from('empresa');
$this->db->join('servicos', 'servicos.id = empresa_id', 'right');
$this->db->join('Habilitacao', 'carteira.id = empresa_id', 'right');
//$this->db->join('Pagamento', 'Pagamento.id = empresa.id');

$data = $this->db->get();
return $data->result();

}

[eluser]alvaroeesti[/eluser]


of course it supports multiple joins

give an alias to each table

empresa e

servicos s

habilitacao h

and join them by them, but I dont see how habilitacao is on the same line as carteira, is carteira a field of habiliitacao?

Code:

$this->db->from(‘empresa e’);
$this->db->join(‘servicos s’, ‘s.id = e.id’, ‘right’);
$this->db->join(‘habilitacao h’, ‘h.id = e.id’, ‘right’);
$this->db->join(‘pagamento p’, ‘p.id = e.id’);

[eluser]leet_2k[/eluser]
I think in your code replace the following:

empresa_id

with

empresa.id

That should fix it up. If you id is empresa_id and your table is empresa then replace the above with:

empresa.empresa_id.

Cheers.

[eluser]Marcelo Wanderley[/eluser]
Grad Student

I will then put the alias to organize.
Thank you for the tip.


Summer Student
I managed to do with his idea, but it generated a huge result.
He views the same record several times.

I resolve to?


Very grateful for the attention


$this->db->select('*');
$this->db->from('empresa');
$this->db->join('servicos', 'servicos.empresa_id = empresa.id');
$this->db->join('Habilitacao', Habilitacao.empresa_id = empresa.id');
$this->db->join('Pagamento', 'Pagamento.empresa_id = empresa.id');

$data = $this->db->get();
return $data->result();

[eluser]alvaroeesti[/eluser]


add the 'right', to restrict results.

Also, we don't know the full schema of your table flow, so, if you have redundancies on your tables you will be getting double results.

We would need to see the whole design and the business logic, but that takes a long time,

we can only help you with the syntax, but we cannot go into the full interrelation of your tables

[eluser]Marcelo Wanderley[/eluser]
Grad Student

Right also not working.

The database schema is as follows.

Empresa - table1

Servicos - table2

Habilitacao - table3

Pagamento - table4


Empresa - table1 -- 1-n -- Servicos - table2

Empresa - table1 -- 1-n -- Habilitacao - table3

Empresa - table2 -- 1-n -- agamento - table4


To list'm using the code below.

print_r ('<pre>'); print_r ($ object); print_r ('</ pre>');


When I put the $ object [0] just ran a query.

print_r ('<pre>'); print_r ($ object [0]); print_r ('</ pre>');

[eluser]alvaroeesti[/eluser]


but that has nothing to do with us. It depends on what values you would get through Right, maybe none, maybe you get them through Left,

In any case, we dont know your ERA (Entity Relationship Analysis) and we don't know if you have normalized the tables up to the 6th level or you have transitive dependencies or you have dependencies amongst non-key fields etc

Database design is not a joke, you really have to have a very clear understanding of how it must be done

[eluser]alvaroeesti[/eluser]


what are all the fields of each table?

[eluser]alvaroeesti[/eluser]
also, why are you joining all tables to empresa?

dont the tables have links between them?

[eluser]Marcelo Wanderley[/eluser]
Lab Assistant,

Below is how I am doing.

the connection is always made with the foreign key empresa_id


Empresa
- id
- nome
- CPF

Hibilitatao
- id
- nome
- tipo
- empresa_id

Servicos
- id
- nome
- descricao
- empres_id

Pagamento
- id
- tipo
- valor
- empresa_id