Yii Framework Forum: Using Csqldataprovider For A Query, Calculate Additional Data And Do Sorting By That Calculated Data - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Using Csqldataprovider For A Query, Calculate Additional Data And Do Sorting By That Calculated Data Rate Topic: -----

#1 User is offline   klammeraffe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 07-September 12
  • Location:Berlin, Germany

Posted 19 December 2012 - 08:01 AM

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?
0

#2 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 19 December 2012 - 08:08 AM

View Postklammeraffe, on 19 December 2012 - 08:01 AM, said:

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.

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?
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#3 User is offline   klammeraffe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 07-September 12
  • Location:Berlin, Germany

Posted 19 December 2012 - 09:01 AM

View PostDa:Sourcerer, on 19 December 2012 - 08:08 AM, said:

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

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?

Quote

Is there any way you can calculate the likeability of a book within SQL?

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.
0

#4 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 19 December 2012 - 09:12 AM

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.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
1

#5 User is offline   klammeraffe 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 07-September 12
  • Location:Berlin, Germany

Posted 28 December 2012 - 07:07 AM

View PostDa:Sourcerer, on 19 December 2012 - 09:12 AM, said:

I'd probably go with pl/sql.

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users