Posted by ltwinner on July 1, 2010 at 10:46am
I have an SQL statement that it seems cannot be replicated in views. It shows the latest blog post for each user, sortable by username, post title or post date. It provides the same functionality as this blog listing page - http://www.cardrunners.com/blog/featured/
I want to make a module based on this statement that will make the /blog homepage look like the one in the site posted above. It is the perfect solution for a community site with alot of users.
Here is the SQL statement -
SELECT node.title, node.created, users.name
FROM node, users
WHERE type = 'blog' AND node.uid = users.uid AND nid IN
(SELECT DISTINCT MAX(nid)
FROM node
WHERE type = 'blog'
GROUP BY uid)
ORDER BY users.name { or node.title/node.created }Is it possible to pass this query to views somehow, skipping the SQL generation step, and just using views to create the table, make it sortable and pageable?
Comments
Use MySQL views and Table Wizard module
This might come quite a bit late, but might help somebody else with a similar problem:
Create a MySQL view for your complex query and make sure you have the first column something that could be used as a primary key (in the query above, simply add the node.nid as first column). Then use the Table Wizard module to surface the MySQL view into the Views module. NOTE: MySQL views can't have a primary key, but the Views module needs one. Table Wizard allows you in the user interface to designate a column as primary key. From there on, when you create a new Drupal View, the MySQL view can be selected as a type of view (like node, user, comment, etc.).
Caveat: Table Wizard is being phased out in favor of Data module, but the Data module is still in alpha and assigning a primary key to a MySQL view doesn't work (yet? as of alpha-12).