Welcome Guest, Not a member yet? Register   Sign In
Active Record + Join SQL Problem
#1

[eluser]PV-Patrick[/eluser]
I am trying to come up with an active record query to meet the following criteria and I can't seem to get it working; or maybe I am going about it the wrong way.

What I am looking to do is get data from 4 tables and sort them by timestamp. Each table shares at least 2 columns and are not exactly the same. Some have more data than others with different column names.

Is it possible to sort the data by timestamp and achieve what I want? I can't seem to get the order_by working the way I would like. If anyone could help or provide me with a simplified example, please let me know, thank you!
#2

[eluser]darkhouse[/eluser]
As far as I know, there's no way to 'combine' the timestamp field in each different table into one field, which is what you're trying to do. If you do sort by table1.timestamp, table2.timestamp, etc... its going to sort all table1 rows by timestamp, and then all table2 rows... it won't mix the tables. I'm not sure this is possible.
#3

[eluser]PV-Patrick[/eluser]
Hum,

What about pulling the data out individually and combining the result arrays into 1 array and then sorting it, is that possible?
#4

[eluser]srisa[/eluser]
It would be helpful if you provide a sample data set, query that you have tried, the result you are getting and the result you are expecting.
#5

[eluser]meigwilym[/eluser]
You need a Foreign Key for each table to another, then it's simply a matter on SELECT FROM table1 INNER JOIN table2 ON table1.fk = table2.id with a ORDER BY thrown in at the end.

If your db design was good it should be no problem.

Mei
#6

[eluser]PV-Patrick[/eluser]
I have been changing this over and over trying to get it to work so this doesn't exactly work how I would like it as I can't get the order_by function to work. This is also the stripped down version of setting alias for items with same column names.

Code:
$this->db->select("table1.*, table2.*");
$this->db->select("table3.*, table4.*");
$this->db->join('table2', 'table1.id = table2.id', 'left');
$this->db->join('table3', 'table1.id = table3.id', 'left');
$this->db->join('table4', 'table1.id = table4.id', 'left');
$this->db->where('table1.id', $id);
//$this->db->order_by('timestamp desc');
$this->db->limit(15);

$this->db->get('table1')
#7

[eluser]meigwilym[/eluser]
Not sure but you may want to try the following. I've put the SELECT into one line (you may have needed a comma after 'table2.*') added a FROM and given the ORDER BY clause a table reference.

Code:
$this->db->select("table1.*, table2.*, table3.*, table4.*");

$this->db->from('table1');

$this->db->join('table2', 'table1.id = table2.id', 'left');
$this->db->join('table3', 'table1.id = table3.id', 'left');
$this->db->join('table4', 'table1.id = table4.id', 'left');
$this->db->where('table1.id', $id);
$this->db->order_by('table1.timestamp desc');
$this->db->limit(15);

$this->db->get()

Also, try the raw SQL version, just to see.

Code:
$sql = "SELECT table1.*, table2.*, table3.*, table4.* FROM table1 LEFT INNER JOIN table2 ON table1id = table2.id LEFT INNER JOIN table3 ON table2.id = table3.id LEFT INNER JOIN table4 ON table3.id = table4.id ORDER BY table1.timestamp DESC LIMIT 15"

$query = $this->db->query();

Whatever you get, there should be an SQL error if there's anything wrong with it.

Cheers,

Mei
#8

[eluser]PV-Patrick[/eluser]
I'll give that a try meigwilym, thank you. However, I have my doubts it will work the way I want as I am trying to sort by ALL the timestamps from the 4 tables and that is only doing it by the first 1 if I read it correctly. I'll give it a try right now and post my results.
#9

[eluser]PV-Patrick[/eluser]
That didn't work... I think what I am looking for is something like a FULL JOIN, but I can't get that to work without giving me an unknown table error. Essentially what I am trying to do is keep all the data in all the tables, even the ones with the same column names and sort them by their timestamp.
#10

[eluser]meigwilym[/eluser]
I think we need to see the schema in order to help you further.

Mei




Theme © iAndrew 2016 - Forum software by © MyBB