Yii Framework Forum: Multiple models Cgridview problem - Yii Framework Forum

Jump to content

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

Multiple models Cgridview problem Rate Topic: -----

#1 User is offline   chumbx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 26-January 12

Posted 10 April 2012 - 03:22 AM

I got several models, that I want to use in 1 Cgridview with filters for them.
The idea is to show a query FULL JOIN that works on Cgridview with his search function.
What's the big issues? The models don't have a relation between them all.
I have 4 tables: Project, Purchases, Sales and Project_hours.
Project_hours, Purchases and Sales got an id_project and each of those tables got a cost that i need.
In case of project_hours, it's the time spent in the project, purchases the cost of the purchases and in the sales I have the income from the sales.
The idea is to show a big table with loads of information about those 4 tables, with a search field and to show the total cost for each project.
Any ideas?

As far I can tell, Cgridview only works for 1 model, and when I used a different search function with the FULL JOIN criteria, I couldn't show in the Cgridview anything besides the project model.

Please I need a big help here if possible, just give me some ideas to work this out.
0

#2 User is offline   chumbx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 26-January 12

Posted 13 April 2012 - 10:17 AM

Can you please help me on this issue?
Can't find a way to do it simple.
I just wanted to have a gridview with a fulljoin working but can't make it to work
0

#3 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,041
  • Joined: 16-February 11
  • Location:Japan

Posted 13 April 2012 - 11:23 AM

Hi chumbx,

Quote

What's the big issues? The models don't have a relation between them all.


1) Probably you may establish HAS_MANY relations.

Project HAS_MANY Purchases
Project HAS_MANY Sales
Project HAS_MANY Project_hours

Those definitions may seem wrong to you, because there are no such FK constraint relations between those tables.
But when you focus on the projects and their costs, I think there's no harm to establish those virtual relations in Project model.

2) You may create a DBMS view and its corresponding CActiveRecord model.

You can create a CActiveRecord not only for a db table but for a db view.

Quote

Each AR class represents a database table (or view)
http://www.yiiframew.../en/database.ar

If you can create a view for your FULL JOIN-ed big table, then you can also create a CActiveRecord model for it.
0

#4 User is offline   chumbx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 26-January 12

Posted 09 May 2012 - 04:23 AM

Ok I didn't understand...
I'm still newbie at this...

I'm trying to use Flexigrid instead, but got the same problem.
Now it only shows a table with different projects, and FULL JOIN shows a table with multiple projects with same name because of the different hours for same project

What can I do?

This is what I use for Flexigrid atm, an echo in JSON:

public function actionPostxml()
	{
		if (isset($_POST['page'])) {
			$page = $_POST['page'];
		}
		if (isset($_POST['sortname'])) {
			$sortname = $_POST['sortname'];
		}
		if (isset($_POST['sortorder'])) {
			$sortorder = $_POST['sortorder'];
		}
		if (isset($_POST['qtype'])) {
			$table = "";
			$qtype = $_POST['qtype'];
		}
		if (isset($_POST['query'])) {
			$query = $_POST['query'];
		}
		if (isset($_POST['rp'])) {
			$rp = $_POST['rp'];
		}
		$total=0;
		$data = array();
		$data['page'] = $_POST['page'];
		$data['total'] = $total;
		$data['rows'] = array();
		$searchSql = ($qtype != '' && $query != '') ? $qtype.' LIKE \'%'.$query.'%\'' : '';

		$model = Project::model()->with(array('ph','sales'))->findAll(array('limit'=>$_POST['rp'],'order'=>$sortname.' '.$sortorder));		
		
		foreach ($model as $m)
		{
			$data['rows'][]=array('id' => $m->id, 'cell' => array($m->name,$m->projectHours->hours,$m->sales->id));
			$total++;
		}
		
		$data['total'] = $total;
		echo json_encode($data);
	}


And the relations in the Project model:

public function relations() {
        return array(
            'pidpid' => array(self::BELONGS_TO, 'Project', 'pid'),
            'projectIdi' => array(self::HAS_ONE, 'ProjectIdi', 'id_project'),
            'projectD' => array(self::HAS_ONE, 'ProjectD', 'id_project'),
            'project' => array(self::HAS_ONE, 'ProjectId', 'id_project'),
            'idCustomer' => array(self::BELONGS_TO, 'Customer', 'id_customer'),
            'idArea' => array(self::BELONGS_TO, 'ProjectArea', 'id_area'),
			'idUser' => array(self::BELONGS_TO, 'User', 'id_user'),
            'projectProgress' => array(self::HAS_ONE, 'ProjectProgress', 'id'),
			'projectHours' => array(self::HAS_ONE, 'ProjectHours', 'id_project'),
			'ph' => array(self::HAS_MANY, 'ProjectHours', 'id_project','joinType'=>'FULL JOIN'),
			'sales' => array(self::HAS_MANY, 'SalesOrder', 'id_project','joinType'=>'FULL JOIN'),
        );
    }

0

#5 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,041
  • Joined: 16-February 11
  • Location:Japan

Posted 09 May 2012 - 07:00 PM

Quote

$searchSql = ($qtype != '' && $query != '') ? $qtype.' LIKE \'%'.$query.'%\'' : '';

$model = Project::model()->with(array('ph','sales'))->findAll(array('limit'=>$_POST['rp'],'order'=>$sortname.' '.$sortorder));          


It seems to me that you are not using $searchSql when you retrieve the projects.
0

#6 User is offline   chumbx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 26-January 12

Posted 10 May 2012 - 09:33 AM

If i add the Sqlsearch it returns this:

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "ph.hours" could not be bound.. The SQL statement executed was: SELECT TOP 15 [t].[id] AS [t0_c0], [t].[id_user] AS [t0_c1], [t].[id_area] AS [t0_c2], [t].[id_customer] AS [t0_c3], [t].[name] AS [t0_c4], [t].[type] AS [t0_c5], [t].[pid] AS [t0_c6] FROM [dbo].[Project] [t] WHERE (ph.hours LIKE '%2%') ORDER BY id asc 


So I was trying first to gather a FULL JOIN there, and a FULL JOIN will repeat same project over and over... and that doesn't happen there on that table. As far I can see, each project has only 1 project hours... It's like 1 to 1 , and I need to check every project hours, even if the project hours repeats himself!
0

#7 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,041
  • Joined: 16-February 11
  • Location:Japan

Posted 10 May 2012 - 09:23 PM

I'm sorry but I can't help you very much, because I didn't have any chance to use FULL JOIN so far. :(

Anyway, I think you would be better try to construct a plain old sql that will satisfy your needs at first, maybe in the IDE of MSSQL. And then come back to yii ...
0

#8 User is offline   chumbx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 26-January 12

Posted 11 May 2012 - 04:22 AM

I was trying to use FlexiGrid but I could use CGridView.
It's the same for me, I just want it to work. What FULL JOIN does, is to join all the 3 tables, and show a row for everything, even if some columns got NULL's.

For instance, for my tables it should say something like:

(PROJECT A) (8 hours) (2 extra hours) (2012-05-15) ....
(PROJECT A) (8 hours) (0 extra hours) (2012-05-15) ....
(PROJECT A) (8 hours) ( ) (2012-05-15) ....

So basically, it joins everything from all 3 tables without requirements.
Why I need it? I want to join the project costs, with user costs per hours/extra hours and sales orders.
So I could check how much a project had spent/won or.... a total for all the projects to check if it was positive or not...

PS: Sorry for my english, I'm portuguese.
0

#9 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,041
  • Joined: 16-February 11
  • Location:Japan

Posted 11 May 2012 - 07:50 PM

Um, anyone?
0

#10 User is offline   chumbx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 13
  • Joined: 26-January 12

Posted 22 May 2012 - 11:43 AM

Any1 here could help please?
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