Using Csqldataprovider For A Query, Calculate Additional Data And Do Sorting By That Calculated Data

Yii 1.1 General Discussion
1

Hey,

I am trying to display a table of Books with one column of calculated data (a prediction of how much one will like it). The table should be sortable also by the prediction and here it all starts.

Since my SQL to retrieve the book list contains things like ‘PARTITION BY’ I am forced to use raw SQL and can’t use ActiveRecords. So I am going with CSqlDataProvider at the moment. Since the prediction column is not included yet I can sort the table with CGridView just fine.

Now I would like to include the predictions which depend on a userid and a bookid. All the calculation is done in a function of a separate class. Someone in the IRC mentioned Virtual Attributes, but from the Wiki I don’t quite see how this can work. I don’t seem to be able to access the single bookids - nor do I see how to make the column sortable later.

Does anyone have an idea how to handle that to include calculated data with VA or without into a sortable table?

2

Huh? Table partitioning should not affect queries at all. I don’t see how that impairs ActiveRecords.

Also: Virtual attributes are an AR thing. You won’t be able to use them with CSqlDataProvider, let alone have your results sorted by a va. Is there any way you can calculate the likeability of a book within SQL?

3

As far as I know thinks like


SELECT row_number() OVER (

	PARTITION BY authorid ORDER BY CASE WHEN languageid = 150 then 1 when languageid = 123 then 2 else 3 END

) AS rn

can’t be done with AR?

The function has a couple of lines - it probably is somehow possible with PL/pgSQL (I’m working with PostgreSQL) but might cause me pain due to a lack of knowledge. If it is anyway the only way how to achieve it, I will try it. But I rather would not.

4

Ah, okay. I confused that with MySQL’s table partitioning which happens at table creation time and has no impact on the syntax of select queries :)

As for your problem: I’m not proficient enough to provide a solid answer. I’d probably go with pl/sql, but others might have better ideas.

5

That is what I did and it solved after quite some work the sorting issue. At least - in the end, also the calculation is faster since done inside of the DBMS.