Latest snippets tagged sql mysqlhttp://snippets.prendreuncafe.com/snippets/tagged/sql+mysql/order_by/date2008-05-15T22:06:43ZSymfonynoreply@symfony-project.comGestion des tags en SQL2008-05-15T22:06:43Z93Un schema classique de gestion de tags :
[code=sql]
CREATE TABLE Project (
project_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
url TEXT NOT NULL,
PRIMARY KEY (project_id)
) ENGINE=MyISAM;
CREATE TABLE Tags (
tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag_text VARCHAR(50) NOT NULL,
PRIMARY KEY (tag_id),
INDEX (tag_text)
) ENGINE=MyISAM;
CREATE TABLE Tag2Project (
tag INT UNSIGNED NOT NULL,
project INT UNSIGNED NOT NULL,
PRIMARY KEY (tag, project),
INDEX rv_primary (project, tag)
) ENGINE=MyISAM;
[/code]
Pour récupérer tous les projets taggué `php` **ou** `mysql` :
[code=sql]
SELECT p.name
FROM Project p
INNER JOIN Tag2Project t2p
ON p.project_id = t2p.project
INNER JOIN Tag t
ON t2p.tag = t.tag_id
WHERE t.tag_text IN ('mysql', 'php');
[/code]
Pour récupérer tous les projets taggué `php` **et** `mysql` :
[code=sql]
SELECT p.name
FROM Project p
CROSS JOIN Tag t1
CROSS JOIN Tag t2
INNER JOIN Tag2Project t2p
ON p.project_id = t2p.project
AND t2p.tag = t1.tag_id
INNER JOIN Tag2Project t2p2
ON t2p.project = t2p2.project
AND t2p2.tag = t2.tag_id
WHERE t1.tag_text = "php"
AND t2.tag_text = "mysql";
[/code]
Tiré et adapté de la présentation [Join-fu de Jay Pipes](http://jpipes.com/presentations/joinfu/joinfu.pdf)[Symfony] Traiter les OR sql avec Propel2007-01-15T12:05:04Z40Voici comment gérer les "ou" SQL dans Propel :
[code]
$criteria = new Criteria();
$criteria->add(TotoPeer::NAME, 'Gérard Bouchard');
$criterion = $criteria->getNewCriterion (
TotoPeer::ID, 5
)->addOr($criteria->getNewCriterion (
TotoPeer::ID, 10
));
$criteria->addAnd($criterion);
TotoPeer::doSelect($criteria);
[/code]
Ceci donnera quelque chose comme :
[code]
SELECT
*
FROM
toto
WHERE
toto.NAME = 'Gérard Bouchard'
AND
(
toto.ID = 5
OR
toto.ID = 10
);
[/code][Symfony] Executer une requête spécifique (custom query) avec Propel2006-11-28T10:10:13Z22[code]
$con = Propel::getConnection();
$stmt = $con->prepareStatement('SELECT foo, bar FROM baz WHERE name=? AND active=?');
$stmt->setString(1, 'MyName');
$stmt->setString(2, '1');
$rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
[/code]
Ou encore plus con :
[code]
Propel::getConnection()->executeUpdate('SET FOREIGN_KEY_CHECKS=0');
[/code]