Multiple models Cgridview problem

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.

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

Hi chumbx,

  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.

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

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

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'),

        );

    }



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

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!

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 …

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.

Um, anyone?

Any1 here could help please?