Multidimensional Joins

[Midnight Modding]

Is there a way to use them for Table\Db? If not, what is my best alternative when I am using joins and needing to create active records involving the resulting data? And see in one case I am joining the same table twice and so without multidimensional joins I would have to alias them and then when creating active records it would be messy.

I don't think there is a way to get multidimensional joins in... Although I did create my own table class extending Table\Db, so maybe I could somehow do it? I havent looked closely yet, but I "think" since the select is buried within a big method within Db.php, I can't really affect multidimensional joins.

[Midnight Modding]

Also I looked through Db.php and want to be sure I am understanding something.

1. it looks like if you don't set any $selects, it automatically selects the entire result row (not just the table row), so if I do a join that has a select 'join_table.*' and I do NOT set $selects, it's going to have the join table's row in the select twice, right? (* and join_table.*). I assume it would just overwrite columns' values with themselves, but just odd that I'd have to use $selects to keep it from having the join table's row listed twice in the select.

2. $onlySelected is a bit confusing too. I see that using it makes the final query only select from $selects... but what gets confusing is why $onlySelected is columns instead of bool, since it just takes columns from $onlySelected and adds them to $selects, anyway. Maybe I'm just toot ired to think it out... I do see $query also ahs different behavior based on $onlySeleected, but I can't quite make out what $query is needed for, anyway. Looks like related to sorting in some way... Oh well i better go sleep, I guess.

[bfarber]

There is no way to use multidimensional joins unless you override the getRows() method.

If you don't specify what to select, we select '*'. This doesn't hurt anything or cause anything to be selected twice or anything like that.

I don't think onlySelected is still used in 4.3. I think it was related to group by support in the table helper, which we removed.

[Midnight Modding]

Oh yeah I remember Matt mentioning the issue with group by.

yes, getRows() is what I was talking about where obviously it would not be a good idea to overwrite that unless absolutely necessary, as long and complicated as it is, to prevent issues with future changes by IPS. I guess I'll just have to select the few columns I need and not bother making an active record out of it... I was going to use its url(), but can just select the few columns I need (and alias, since it's the same table joined twice) anmd manually do the url with internal().

The nature of how the query is done in getRows() makes it where I don't think I could do a multimensional joins by simply doing work before or after the method, either. It would have to be an entire overwrite.

What I meant is if it selects row and then i select the joiner row, it's going to make the select be '*, joiner_row.*', whereas the joiner row would have already been part of *. I know it's going to technically be the same final result, since multidimensional joins are not involved, but was just curious if it slowed anything down because isn't it technucally doing extra work and then overwriting? 

[Midnight Modding]

I've been having to get creative, such as pulling out the data I need, putting it into an array, and then using constructFromData() that way... A bit more to it when it's 2 joins on the same table, but oh well luckily it was only a few columns I needed to use with the object.

[Midnight Modding]

I keep forgetting in the final query it defaults to table.* if you have $selects and the join selects are already in $selects. There's another query before that one that got me mixed up because it had *.

[Midnight Modding]

edit: well I posted about how I wish some things about select and join were documented in dev docs, where I wouldn't have had to run into issues, but I see at some point they "did" add to the database selecting documentation to point out that selecting one column returns a string. It's pretty buried in the doc, though. I did already know that part for a while now, but I had to do a lot of trial and error to figure out that 1. first() isn't the only way it returns only a string and 2. if you select one column from tableOne and join tableTwo and it has no matches in tableTwo, it will still return an array, even though technically you only have 1 legit value.

The part that got me messed up, though, is not documented in dev docs. it's that a join returns all the columns of the joined table, even if it didn't match, and sets them all as null! That is far different than 3.x and now I am going to have to search for all of my isset() in my files, since I probably assumed if there was no match in the joined table, it would simply not return anything at all for that joined table.

[Midnight Modding]

Posting as a reminder to myself and others who haven't tested this stuff... if you use multidimensional joins and only select one column, it returns it as an array, not just the string. Don't use multidimensional joins and select only one column, it returns as a string. And regardless of whether you use multidimensional joins or not, if you select anything from the joined table, you'll receive it's columns (null if it didn't match) and your decision on using multidimensional joins will determine if it's all in one array or a separate arrays per table.

Easy to get mixed up on this. But now I think I have it straight. lol. 

Luckily it looks like I usually didn't use isset() unless i did isset($row['something']) && $row['something']. But I do see the main area I will have to change things... in each of my cFD() methods, I had set properties in the object to be the joined table arrays and I THOUGHT if they didn't match there would be nothing there. So later checks are going to be screwy because if( $obj->prroperty ) will always be TRUE just by the fact of selecting it, since IPS has select returning an array of null values for it. So I'm going to have to find all of those and change it to if( $obj->prroperty->otherProperty ) and since it never had isset() I can't quickly search and find these!

Also, I thought $member->name was using name as an alias. I didn't realize members_display_name was actually changed in the db to name. lol. Obviously that was a quick fix, though.