Hello!

Nowdays I making my website, whichone needs a tabled list of bloggers' and blog entries stats. Designed headers are: author (linked authors' blog view), last entry (title, node link), posted (datetime), count of entries (integer).

Now installed the Views Calc to hack a subquery in to the result qurey. I know, it's ugly think - but(!) it generates this query:

SELECT distinct users.name AS users_name,
   (SELECT COUNT(node.nid) FROM _spdr2_node node, _spdr2_users users
 WHERE (users.uid = node.uid) AND (users.name = users_name) AND (node.status <> 0) AND (node.type IN ('blog'))) AS cid2
 FROM _spdr2_node node 
 INNER JOIN _spdr2_users users ON node.uid = users.uid
 WHERE (node.status <> 0) AND (node.type in ('blog'))
 ORDER BY users_name

So, it's works. But it's ugly and expensive (not optimal). I will make the other two views calc field based on the subselect, and view displays what I think.

My question is: Do you have any other ideas to goal my designed result? Any tips will be wellcomed.

Thanks your response,
prometheus

Comments

prom3theus’s picture

prom3theus commented

I'm sorry for that, but I miscalculated me on the generated view query. So this way isn't the way for solve my problem. Details below...

Fields are:

User: Name
Views Calc: Entries count Entries count
Views Calc: Entry's UID Entry's UID
Views Calc: Entry's NID Entry's NID
Views Calc: Entry's title Entry's title
Views Calc: Entry's created Entry's created
Global: Custom link field Author
Global: Custom link field Last Entry

Result table is:

Entries count_|_Entry's UID_|_Entry's NID_|_Entry's title_|_Entry's created_____|_Author____________________________|_Last Entry_____________
25____________|_1___________|_26__________|_title-A-1_____|_2009-01-07 12:21:41_|_prometheus => "blogs/list/[cid3]"_|_[cid5] => "node/[cid6]"
25____________|_1___________|_26__________|_title-A-1_____|_2009-01-07 12:21:41_|_prometheus => "blogs/list/[cid3]"_|_[cid5] => "node/[cid6]"
... upper row multiplied by 25 becouse it's count ...
1_____________|_4___________|_69__________|_title-B-1_____|_2009-01-17 18:53:42_|_douser => "blogs/list/[cid3]"_____|_[cid5] => "node/[cid6]"

Generated query is:

SELECT DISTINCT(node.nid) AS nid,
   users.name AS users_name,
   (SELECT COUNT(node.nid) FROM _spdr2_node node, _spdr2_users users
WHERE (users.uid = node.uid) AND (users.name = users_name) AND (node.status <> 0) AND (node.type IN ('blog'))) AS cid2,
   (SELECT users.uid FROM _spdr2_node node, _spdr2_users users
 WHERE (users.uid = node.uid) AND (users.name = users_name) AND (node.status <> 0) AND (node.type IN ('blog')) LIMIT 1) AS cid3,
   (SELECT node.nid FROM _spdr2_node node, _spdr2_users users
 WHERE (users.uid = node.uid) AND (users.name = users_name) AND (node.status <> 0) AND (node.type IN ('blog')) ORDER BY node.created DESC LIMIT 1) AS cid6,
   (SELECT node.title FROM _spdr2_node node, _spdr2_users users
 WHERE (users.uid = node.uid) AND (users.name = users_name) AND (node.status <> 0) AND (node.type IN ('blog')) ORDER BY node.created DESC LIMIT 1) AS cid5,
   (SELECT FROM_UNIXTIME(node.created) FROM _spdr2_node node, _spdr2_users users
 WHERE (users.uid = node.uid) AND (users.name = users_name) AND (node.status <> 0) AND (node.type IN ('blog')) ORDER BY node.created DESC LIMIT 1) AS cid4
 FROM _spdr2_node node 
 INNER JOIN _spdr2_users users ON node.uid = users.uid
 WHERE (node.status <> 0) AND (node.type in ('blog'))

On the result table you can see a replace bug in column "Author" and "Last Entry" (custom link fields was not replaced by calculated fields value in result). And distinct not available for "User: Name" field, only to "Node: Nid" but "User: Name" is the first field in list...

So now i'm totally confused.

merlinofchaos’s picture

merlinofchaos commented

I'm not sure what to say. it seems like maybe this should go in the views calc queue.

domesticat’s picture

domesticat commented
Status: Active » Closed (fixed)

Closed while closing all Views support requests with no activity for 6 months. If you still need help, you may re-open this issue at any time.