Why is my SQL "expanded"?

R_J

I have that:

    public function messagesController_beforeConversation_handler ($Sender) {
        $Locations = Gdn::Database()->SQL()
            ->Select('cl.UserID, cl.Longitude, cl.Latitude, cl.DateUpdated, cl.Body')
            ->From('ConversationLocation cl')
            ->GetSelect(); // for debugging
            // ->Where('ConversationID', $Sender->EventArguments['Message']->ConversationID)
            // ->Get()
            // ->ResultArray();    
decho($Locations);        

and get a fatal error because tables UserConversation and User are added to my SQL:

Column 'ConversationID' in where clause is ambiguous

select u.UserID as `UserID`, cl.Longitude as `Longitude`, cl.Latitude as `Latitude`, cl.DateUpdated as `DateUpdated`, cl.Body as `Body`, uc.LastMessageID as `LastMessageID`, uc.CountReadMessages as `CountReadMessages`, uc.DateLastViewed as `DateLastViewed`, u.Name as `Name` from GDN_ConversationLocation cl, GDN_UserConversation uc join GDN_User u on uc.UserId = u.UserId where ConversationID = :ConversationID

I cannot see which function in /library/database is responsible for that so that I could surpress it.

Can anyone help me out?

peregrine

what version of vanilla?

x00

The actual event is BeforeAddConversation the only other simular event is BeforeConversationMeta

First Gdn::Database()->SQL(), Gdn::SQL() and $Sender->SQLare equivalent in this context.

If you are inject in the middle of a query being built you would expect it to end up like that.

Secondly if you have a new kind of data best do it in the context of a model with methods. Rather cluttering the the controller space with sql calls.

peregrine

re: getselect

  /**
    * Builds the select statement based on the various collections in this
    * object. This method should not be called directly; it is called by
    * $this->Get() and $this->GetWhere().

try check this->_Froms

and _FromTables

https://github.com/vanilla/vanilla/blob/2.1/library/database/class.sqldriver.php#L835

R_J

@peregrine said:
what version of vanilla?

The current one, what else!

R_J

@x00 said:
The actual event is BeforeAddConversation the only other simular event is BeforeConversationMeta

I want to add something to a conversation (it's funny that there are no AfterSomething events in conversations application by the way). BeforeConversation brings my output on top of a conversation.

First Gdn::Database()->SQL(), Gdn::SQL() and $Sender->SQLare equivalent in this context.

That's good to know! I would have thought I start a new Sql query when not using $Sender->SQL

If you are inject in the middle of a query being built you would expect it to end up like that.

That's what I would have expected, but I've put a Gdn::Database()->SQL()->Reset(); before my query, tried decho(Gdn::Database()->SQL()->GetSelect()); die;which gave me a simple "select *" and that's why I'm pretty sure, that I'm not conflicting with an existing SQL. That was my first thought when I saw that ugly error message

Secondly if you have a new kind of data best do it in the context of a model with methods. Rather cluttering the the controller space with sql calls.

Yes, that would be way more cleaner and I would be full of shame if anybody could see the code I'm working with! I've started with models and views mixed in the controller and in this case I've even got inline css and inline js in my "inline" view.
But I've got an excuse! I'm still testingand as soon as it is getting serious, I will be more structured!

R_J

@peregrine said:
re: getselect

try check this->_Froms

and _FromTables

https://github.com/vanilla/vanilla/blob/2.1/library/database/class.sqldriver.php#L835

That's where I was looking at, but I thought there is some automagical mapping when my table has a column UserID and a column ConversationID.

But maybe it is really that I'm reusing an existing SQL. Maybe I'll investigate where my Event is fired...

R_J

BeforeConversation is called in the middle of the messages controller index view, so I guess it could not have an "open" $Sender->Sql, right?

x00

Try to keep model/sql out of view or most controller logic. Views are for presentation not for heavy logic, instead put it in Data. that is also important, becuase Data is used in other DeliveryMethods also view is far too late for getting data.

i sound like you a hook has a an implement sql call. I would check your plugins.

R_J

OH MY GOD! Please delete this question out of your memories: it really has been influenced by another plugins messing around, but that "other" plugin was the same plugin: I've tried that sql in another function call before.

Oh what a shame...

peregrine

@R_J said:
OH MY GOD! Please delete this question out of your memories: it really has been influenced by another plugins messing around, but that "other" plugin was the same plugin: I've tried that sql in another function call before.

Oh what a shame...

Please delete this question out of your memories

it still provided some interesting feedback. And your questions are always thought provoking and interesting.

I've tried that sql in another function call before.

it was elegant though at least, I hope it was.

x00

yep views are not the place for this logic.

R_J

I'm now cleaning up the code before I do anything else with it. If it would be halloween, I would have posted it in order to scare the hell out of you