Welcome Guest, Not a member yet? Register   Sign In
Active Records return values whit "as" on the query string
#1

[eluser]Ghostika[/eluser]
Hi there,
my problem is that, I have a query string with JOIN-s and I use the Active Records class,
the code is something like this:
Code:
SELECT *
FROM (users as u)
LEFT JOIN pontozobiro as p ON u.id = p.user_id
LEFT JOIN edzo as e ON u.id = e.user_id
LEFT JOIN szamlalo as sz ON u.id = sz.user_id
LEFT JOIN tisztsegviselo as t ON u.id = t.user_id
LEFT JOIN versenyfelugyelo as v ON u.id = v.user_id
WHERE `u`.`id` = 1

And after I have the result, I don't have the variables like $ret['u.id'] insted only $ret['id'], so it doesn't take consideration the "as" clause. Where is the problem?
This is a problem for me, cause I have some variables, what have the same names in the different culloms.
Thanx
#2

[eluser]toopay[/eluser]
Consider to redesign your database. If, for any reason you need to join five tables in one query, thats not really a good option.
#3

[eluser]Ghostika[/eluser]
I need the 5 tables, cause they are 5 different rolles, and for different rolls I need different datas.
What I don't get, Is why can't I use the name, what I gaved in the "as" part?!
#4

[eluser]jmadsen[/eluser]
[quote author="toopay" date="1313120045"]Consider to redesign your database. If, for any reason you need to join five tables in one query, thats not really a good option.[/quote]

Sorry, but that's silly.


@Ghostika - yes, it ignores the table prefix, so you need to do column aliases in your SELECT clause.

This is probably a good idea for you anyway, because if you are using 5 tables, it is unlikely you want every single column from all five tables, so "SELECT *" is a bad habit that will slow your result set return (all those extra values still have to go through a "pipe" just like if you were passing a lot of javascript comments over the internet. keep it pared down to only what you need ).
#5

[eluser]toopay[/eluser]
[quote author="jmadsen" date="1313134341"][quote author="toopay" date="1313120045"]Consider to redesign your database. If, for any reason you need to join five tables in one query, thats not really a good option.[/quote]

Sorry, but that's silly.
[/quote]
For me, i prefer have to explain for someone who (may slipped away and/or) didnt get my point or see that from "different perspective", about what my previous statement/post means, rather than teach poor programming practices.

Look at his/her query above, what i see is ineffective database schema design, since when someone try joining up to five tables with just one field reference like that(one-to one relationship), thats more likely he just need to have one table instead.

I try to have consistent argument here, everytimes whenever peoples started discussion about queries : JOINs are expensive and it most typical the fewer tables (for the same database) you join the better performance you will get(Those peoples which didnt really see their JOIN queries create a madness, because (*Sigh) their lack of experience, since many of them ussually didnt care about something like this until their database getting more bloated.)
#6

[eluser]jmadsen[/eluser]
I didn't notice he was making a "star" formation all joined on user_id, so you are probably right for this case.

but your statement that "If, for any reason you need to join five tables in one query, thats not really a good option. " is too simplified, and not a good "rule of thumb".
#7

[eluser]toopay[/eluser]
You can read these thread, for more "simplified" and "not a good rule of thumb" posts from me then... Tongue
#8

[eluser]danmontgomery[/eluser]
[quote author="toopay" date="1313150533"]JOINs are expensive and it most typical the fewer tables (for the same database) you join the better performance you will get[/quote]

This is certainly true, but 5 joins will almost always be less expensive than 6 queries. And while there's most likely some optimization he could do, "redesign your database" is not helpful in this regard nor does it even attempt to answer his question.

jmadsen touched on it, but I don't see a full explanation here.

Aliasing a table in your query provides an alias within the query, but only the column names are returned in the result. When the result is returned to you in PHP, it comes in either an object or an array. PHP is unable to assign non-unique object or array keys, so each duplicate column overwrites the previous one. What you are left is the last set of unique column names from your result.

To get around this, as jmadsen says, you should really only be selecting what you need, and you can alias the column names as needed.

Code:
$this->db->select('table_a.*, table_b.id AS tb_id, table_c.other_field AS tc_field');
#9

[eluser]toopay[/eluser]
@noctrum
Thanks for remind me to formulate a specific answer in line with the Q&A nature of this site, which i should did (like what you did above) before give a "redesign your database" sentences as a conclusion(which i think was his real problem with his app and database design --look at his queries above!).

Also
[quote author="noctrum" date="1313175811"]5 joins will almost always be less expensive than 6 queries. [/quote] still sounds like biased and debatable statement, since in "real world" application, this things is "avoidable" (by perform database normalization, etc). I don't mind seeing people suggest each other to use "JOIN" statement everywhere in their Model, as long as they know exactly what he/she did and how to handle it right (like using MapReduce). Otherwise, its always would be a bad suggestion.
#10

[eluser]Ghostika[/eluser]
First, thank you for your technical answer for my problem.

And, since you are intrested in, why I use 5 JOIN, I will try to explain it, but sorry for my English Smile
So, every different table is for a different roles. I need them, cause I don't know, in the future a user will have which one of them. Maybe a user will use only 2 rolls, maybe one, maybe 5 of them, and I need to show every fields on one page, on different tabs. So this is why I did it like that.




Theme © iAndrew 2016 - Forum software by © MyBB