I’d like to issue out the following SQL statement (and similar ones)
SELECT * FROM product_descriptions
WHERE product_id = 2
UNION
SELECT ‘2’ AS product_id, languages.id AS language_id, NULL , NULL
FROM languages
WHERE id NOT
IN (SELECT language_id FROM product_descriptions WHERE product_id = 2)
I’m working on building a data entry screen for database language entry. I want to ensure that it includes both the current languages already stated, as well as language rows for those languages which don’t yet have a database entry.
and it is possible … I’m using the following code to make it a little more dynamic for re-use. The only thing I haven’t figured out yet is how to auto specify a for/loop for the number of null columns for blank records
public function primaryKey() {
return array('key' => 'weight_class_id', 'language' => 'language_id');
}
public function tableName() {
return 'weight_class_descriptions';
}
public function languagesTable() {
return 'languages';
}
public static function model($className=__CLASS__)
{
return parent::model($className);
}
public static function findAllWithLanguages($id) {
$primaryKeys = self::primaryKey();
return self::model()->findAllBySql('SELECT * FROM '. self::tableName() .' WHERE '. $primaryKeys['key'] .' = '. (int)$id
.' UNION SELECT \''.(int)$id.'\' AS '.$primaryKeys['key'].', id AS '.$primaryKeys['language'].' , NULL , NULL'
.' FROM ' . self::languagesTable() . ' WHERE status = 1 AND id NOT IN ('
.' SELECT ' . $primaryKeys['language'] . ' FROM ' . self::tableName() . ' WHERE ' . $primaryKeys['key'] .' = '.(int)$id.')');
}