[Solar-talk] Selecting Cols w/ eager

[Raymond Kolbe]

>
> IIRC you use the relationship name as the table name; e.g., on a
> related "foo", use "foo.colname".  So in the above example you would
> ask for 'cols' => array('user.id', 'user.name'), and not
> "users.colname".


I'm not sure if we are on the same exact page. When I specify 'cols' in my
controller (and pass it to fetch*()) I get an error back stating that MySQL
could not find the column example_model.user.id (and so on)(example_model
now being the name I gave the model). This makes sense since they key 'cols'
specifies the columns only to the direct model/table you are working with
and does not select specific columns from the related models/tables.

So the (current) normal SQL would read something like:

SELECT
  example.id AS example__id,
  example.name AS example__name,
  user.id AS user__id,
  user.name AS user__name,
  user.passwd AS user__passwd,
FROM  example AS example_model
LEFT JOIN users AS user ON example_model.example__name = user.name

and so on and so forth. The above SQL shows how a model relates to another
model and returns ALL columns from all models of interest. If I wanted to
specify 'cols' => array('user.id', 'user.name') just to return id and name
(and not passwd) from the user model/table I get the following error:

Column not found: 1054 Unknown column \'example_model.user.id\' in \'field
> list\'
>

As you can see, Solar takes the 'cols' key that I pass it, and prepends the
model name to the columns I wish to select only. This does not work when I
wish to select specific columns from the related models/tables.

My first thought to fixing this (not that it is "broken") was to specify the
columns of the related models under 'eager' so that my params passed to
fetch*() would look something like:

$params = array(
  'eager' => array(
    'user' => array(
      'cols' => array('id', 'name'),
    ),
  ),
  'where' => "example_model.name = {$posted_user_name}",
  'order' => "example_model.id ASC",
);

It looks real ugly to be honest but it looks like in Solar newSelect() is
used to to process any relationships (the 'eager' key). If newSelect could
take care of processing the multidimensional array (e.g. the 'cols' key
under 'egaer') I think this may solve my issue (and maybe other people out
there).

But like I said in one of the last emails, right now setting the 'cols' key
in the actual model itself isn't too bad of an idea. It just isn't as
flexible on the fly in the controller.

Did I lose anyone? Suggestions and comments are very welcome ;-)

Thank you,
Raymond K.

On Nov 19, 2007 1:12 PM, Paul M Jones <pmjones at ciaweb.net> wrote:

>
> On Nov 19, 2007, at 12:01 PM, Raymond Kolbe wrote:
>
> >> IIRC you use the relationship name as the table name; e.g., on a
> >> related "foo", use " foo.colname".  So in the above example you would
> >> ask for 'cols' => array('user.id', 'user.name'), and not
> >> "users.colname".
> >
> > users is my table name (sorry for the confusion)...so users.id
> > would be the col in that table.
>
> Correct, but (iirc) the SQL gets constructed using the relationship
> name as the alias for the table.
>
> If you turn on SQL profiling ($sql->setProfiling(true)), run the
> models, and then get the profile ($sql->getProfile()) you should see
> that the FROM and/or JOIN are on "users AS user".  So the fully-
> qualified name ought to be "user" in that case.
>
> Hope that made sense, and that I recalled correctly.  ;-)
>
>
> >> Alternatively, you can set up a "has-one limited_user" and define the
> >> limited_user relation cols to be only the cols you want.  That can be
> >> in addition to the regular "user" when you want all the cols.
> >
> > I think this would be the best option right now. However, I think I
> > would like to see a way to call the cols on the fly for all related
> > models/tables involved.
>
> Yeah, I can see now that this would be quite useful.  Will involve
> some rewriting of how queries for the related tables are generated,
> but not a big deal given the usefulness of it.
>
> Can you do me a favor?  Enter this as a feature request at <http://
> solarphp.com/trac> so I don't forget.
>
> Thanks!
>
>
>
> --
>
> Paul M. Jones  <http://paul-m-jones.com>
>
> Solar: Simple Object Library and Application Repository
> for PHP5.  <http://solarphp.com>
>
> Join the Solar community wiki!  <http://solarphp.org>
>
> Savant: The simple, elegant, and powerful solution for
> templates in PHP.  <http://phpsavant.com>
>
>
> _______________________________________________
> Solar-talk mailing list
> Solar-talk at lists.solarphp.com
> http://mailman-mail3.webfaction.com/listinfo/solar-talk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman-mail3.webfaction.com/pipermail/solar-talk/attachments/20071119/a10d5b68/attachment-0001.html