[development] hook_views_query_alter

[Ashraf Amayreh]

Hello Earl,

I doubt that would work because I can't structure any view that would add a
filter/argument on the files.filename at all. I'll explain my view a bit
more. The view takes two arguments, content_type/tid. For example news/5,6.
I've managed to also control the sort using a 3rd argument, so a call would
look like news/5,6/latest or news/5,6/most-viewed

The special case I have is in a term that designates a document type, let's
say the term name is "doc" (meaning get the document nodes that have an
attachment ending with .doc). So the view here would be document/6,7/latest.
6 would be the tid for document type, and 7 would be a tid for something
else from another vocabulary that doesn't have any special case. Let's
presumably say the query generated was:

SELECT n.nid, n.title FROM node n
INNER JOIN term_node tn ON n.nid = tn.nid
WHERE n.type = 'document' AND tn.tid IN (6,7)
ORDER BY n.created DESC

Now with my special case, the query would need to change to:

SELECT n.nid, n.title FROM node n
INNER JOIN term_node tn ON n.nid = tn.nid
INNER JOIN content_type_document ctd ON n.nid = ctd.nid
INNER JOIN files f ON ctd.field_documet_file_fid = f.fid
WHERE n.type = 'document' AND tn.tid IN (7) AND f.filename like '%.doc'
ORDER BY n.created DESC

So I would need to add two more tables to the join, and would need to add a
where clause, and remove part of the tid argument (removing 6). I was able
to add the join to content_type_document by adding a field from this table
to the fields list (although I can't be sure of the effect this will have on
the calls that are for a content type other than document which do not have
this field in their cck, but I would think views handles this by doing a
LEFT JOIN rather than INNER JOIN as I have presumed in the above example). I
hope this clarifies what I'm attempting to do, if I got the approach to this
problem totally wrong then any other approach to this is highly appreciated.



> On 10/29/2010 4:18 PM, Ashraf Amayreh wrote:
> > Hello all,
> >
> > I've searched the net all over for the particular case I have but
> > couldn't find an answer. I have a view with an argument, based on this
> > argument, I would like to either pass it through, or join to two more
> > tables and add a where clause to my query that is pretty unrelated to
> > the argument type. For example, let's say the argument is a tid and I
> > want to add a where clause to the files table if this tid was equal to
> > 5, otherwise, I want it to function normally. For this I looked into the
> > hook_views_query_alter hook
> >
> > The problems I faced in order:
> >
> > 1. The cck file field that contains the fid was not in the fields list,
> > so the table wasn't even in the joins, I resolved this by adding the cck
> > file field to the fields list although I have no use for it. But this at
> > least added the table to the view object in this hook (I didn't really
> > want to add a field in vain just to get its table to show up in the
> > view->query object).
> > 2. After doing that, I needed to do a join from the table in #1 to the
> > files table, again, the files table was not inside the view->query and
> > this is where I got stuck.
> > 3. Assuming #2 works, I will finally add a where clause to the files
> table.
> > 4. Let's say I did all this successfully, I would need to remove the tid
> > that was passed in the argument so that it would not be rendered inside
> > the query (i.e. where tid = 5).
> >
> > Is there any guidelines on how to fully handle the view, view->query
> > objects? All I could find were hacks to add sort by, add simple wheres
> > to tables that were already in the views object, and to create aliases
> > for tables that are, again, already in the view object. None of them
> > with the complexity I need.
> >
> > I've hesitated on weather this should be in the views issue queue or
> > here, but I think many agree views is no longer an optional module. If I
> > do get this resolved I'll be more than happy to post it on our site or
> > to create a views issue with the details.
>
> Can you accomplish what you want to do with two different views? Create
> your own page handler, examine your argument, and pick which display to
> use? Or write an access plugin, which is really very rare, which does
> this, and let Views pick the display based upon the access status?
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20101030/b2dbbb70/attachment.html