Many to Many - how to get a withXXXX($criteria, $value) with filtered results

[albans]

Hello,
I've 2 entities:
- VideoRecord
- CategorieRecord
A video can be associated to multiple categories, and a category can be associated to multiple videos  (many to many).

So, if I want to get videos that are part of the category id 2, I would write something like this:
VideoRecord::finder()->withCategories('id = ?', 2)->findAll()

This works fine, but as the withXXXX() methods doesn't use an "inner join", this returns every videos with a "null" category when the id_category is not 2.

Is there a way to do the same, but without including the videos with the "null" category?
In other words, a way to have similar objects with a "inner join"-like feature on the relation between the 2 entities?

THANKS for your help!

[Aquatic]

I ususally use a mysql map and define the correct statements myself. Have never used withXXX before.

[albans]

Okay, and is it possible to integrate sqlmap to an ActiveRecord class?
Do you have any example?

THANKS!

[Aquatic]

Application.xml:

Code:

<module id="sqlmap" class="System.Data.SqlMap.TSqlMapConfig" EnableCache="false" ConfigFile="Application.data.sqlmap" ConnectionID="db" />

SQL MAP Init:

Code:

$this->sqlmap  = $this->Application->Modules['sqlmap']->Client;   (can be stored in any variable)

Call to SQL MAP:

Code:

$para = array('end' => $end->format($this->_format_db), 'start' => $start->format($this->_format_db));
$data  = $this->sqlmap->queryForList("TimeDiff", $para);

$para: if you need to pass parameters to the sqlmap (used with $param$ inside the map), pass them in an array like the above (array(key => value))
call to queryForList function on the sqlmap object, (there is also queryForObject, if you just need one result), "TimeDiff" in this case is the ID of the statement in the sqlmap

SQL MAP: (data/sqlmap.xml)

Code:

<?xml version="1.0" encoding="UTF-8" ?>
<sqlMapConfig>
<sqlMap resource="maps/sqlmap.xml" />
</sqlMapConfig>

SQL MAP: ( data/maps/sqlmap.xml)

Code:

<sqlMapConfig>
<select id="TimeDiff" resultClass="DayOverviewSummary">   <=== ResultClass is your AR Class
         SOME REGULAR SQL STATEMENT      
</select>
</sqlMapConfig>

Hope this helps.

[albans]

MANY (to MANY) THANKS!!!  
Your informations really helped me out!

I ended up with a control including a repeater that manage pager using SQLMap...
I think this is interessant as it took me a while to get everything working well, so here's some part of my code.

In protected/application.xml

Code:

<module id="sqlmap" class="System.Data.SqlMap.TSqlMapConfig" EnableCache="false" ConfigFile="Application.database.sqlmap" ConnectionID="dbVod" />

In protected/database/sqlmap.xml

Code:

<?xml version="1.0" encoding="UTF-8" ?>
<sqlMapConfig>
<sqlMap resource="maps/VideoRecord.xml" />
</sqlMapConfig>

Note: 3 different queries are used: one to get all rows, one for a certain "page" of rows, and the last one to count the rows.
In protected/database/maps/VideoRecord.xml

Code:

<sqlMapConfig>
<select id="SelectVideosByCategoryAndStatus" resultClass="VideoRecord" parameterClass="SelectVideosByCategoryAndStatus_Param_Item">
select v.*
from video as v
inner join categorie_video as c on v.id = c.id_video
where c.id_categorie = #idCategorie#
and v.id_status = #idStatus#
order by #orderBy# #order#
</select>
<select id="SelectVideosByCategoryAndStatus_Pager" resultClass="VideoRecord" parameterClass="SelectVideosByCategoryAndStatus_Param_Item">
select v.*
from video as v
inner join categorie_video as c on v.id = c.id_video
where c.id_categorie = #idCategorie#
and v.id_status = #idStatus#
order by #orderBy# #order#
limit #limit# offset #offset#
</select>
<select id="SelectVideosByCategoryAndStatus_Counter" resultClass="int" parameterClass="SelectVideosByCategoryAndStatus_Param_Item">
select count(v.id)
from video as v
inner join categorie_video as c on v.id = c.id_video
where c.id_categorie = #idCategorie#
and v.id_status = #idStatus#
</select>
</sqlMapConfig>

In protected/database/VideoRecord.php

Code:

<?php
class VideoRecord extends TActiveRecord
{
const TABLE='video';

public $id;
public $title;
public $description;
public $date_added;
public $duration_sec;
public $views;
public $id_publisher;
public $id_status;

public $status;
public $publisher;
public $files=array(); 
public $categories=array(); 
public $tags=array();

public $videos=array();

public static $RELATIONS=array
    (
'status' => array(self::BELONGS_TO, 'VideoStatusRecord'),
'publisher' => array(self::BELONGS_TO, 'UserRecord'),
'files' => array(self::HAS_MANY, 'VideoFileRecord', 'id_video'),
        'categories' => array(self::MANY_TO_MANY, 'CategorieRecord', 'categorie_video'),
'tags' => array(self::MANY_TO_MANY, 'TagRecord', 'tag_video')
    );

public static function finder($className=__CLASS__)
{
return parent::finder($className);
}
}

/* sqlMap classes, see ./maps/VideoRecord.xml */
class SelectVideosByCategoryAndStatus_Param_Item
{
public $idCategorie;
public $idStatus;

public $orderBy = 'views';
public $order = 'asc';
public $offset = 0;
public $limit = 0;
}
?>

In protected/pages/controls/CategoriesListVideo.tpl

Code:

<ul class="videoList">
<com:TRepeater ID="rpVideos"
ItemRenderer="Application.pages.videos.CategoriesListVideoRenderer"
AllowPaging="true"
AllowCustomPaging="true"
/>
 </ul>
<com:TPager ControlToPaginate="rpVideos" OnPageIndexChanged="pageChanged" />

Note: this is where the repeater pager is managed.
In protected/pages/controls/CategoriesListVideo.php

Code:

<?php
class CategoriesListVideo extends TTemplateControl {
protected $ID_Category = 0;

protected $sqlmap;
protected $sqlmap_param;
protected $PageSize = 30;
protected $NbVideos = 0;

public function onInit($param)
    {
        parent::onInit($param);

$this->ID_Category = (int)$this->Request['id'];

$this->sqlmap = $this->Application->Modules['sqlmap']->Client;
$this->sqlmap_param = new SelectVideosByCategoryAndStatus_Param_Item;
$this->sqlmap_param->idCategorie = $this->ID_Category;
$this->sqlmap_param->idStatus = 2;

$this->NbVideos = $this->sqlmap->queryForObject("SelectVideosByCategoryAndStatus_Counter", $this->sqlmap_param);

$this->rpVideos->PageSize=$this->PageSize;
$this->rpVideos->VirtualItemCount = $this->NbVideos;
 
$this->populateData();
    }

public function pageChanged($sender,$param)
{
$this->rpVideos->CurrentPageIndex=$param->NewPageIndex;
$this->populateData();
}

protected function populateData()
{
$offset=$this->rpVideos->CurrentPageIndex*$this->rpVideos->PageSize;
$limit=$this->rpVideos->PageSize;
if($offset+$limit > $this->rpVideos->VirtualItemCount)
$limit=$this->rpVideos->VirtualItemCount-$offset;
$this->rpVideos->DataSource=$this->getVideos($offset,$limit);
$this->rpVideos->DataBind();
}

protected function getVideos($offset, $limit)
{
$this->sqlmap_param->limit=$limit;
$this->sqlmap_param->offset=$offset;

if (($offset > 0) && ($limit > 0))
{
$query_name = 'SelectVideosByCategoryAndStatus_Pager';

}
else
{
$query_name = 'SelectVideosByCategoryAndStatus';
}

return $this->sqlmap->queryForList($query_name, $this->sqlmap_param);
}
}
?>

This works fine with TRepeater and TPager.
Thanks again for the help.
Bye.

PS: I added an article in FRENCH to my blog covering this subject http://support.oxegena.ch/blog/prado-sqlmap-trepeater-et-tpager-requetes-sql-personnalisees-avec-pagination/26