private function filter(JO_Db_Select $query, $data)
{
if (isset($data['filter_id']) && $data['filter_id']) {
$query->where('id = ?', (int) $data['filter_id']);
}
if (isset($data['filter_name']) && $data['filter_name']) {
$query->where('name LIKE ?', '%' . $data['filter_name'] . '%');
}
return $query;
}
/**
* @param JO_Db_Select $query
* @param array $data
* @return JO_Db_Select
*/
private static function FilterBuilder(JO_Db_Select $query, $data = array())
{
$db = JO_Db::getDefaultAdapter();
if (isset($data['filter_pin_id']) && $data['filter_pin_id']) {
$query->where('p.pin_id = ?', (string) $data['filter_pin_id']);
}
if (isset($data['filter_user_id']) && $data['filter_user_id']) {
$query->where('p.user_id = ?', (string) $data['filter_user_id']);
}
if (isset($data['filter_fullname']) && $data['filter_fullname']) {
$query->where('u.firstname LIKE ? OR u.lastname LIKE ?', '%' . $data['filter_fullname'] . '%');
}
if (isset($data['filter_username']) && $data['filter_username']) {
$query->where('u.username LIKE ?', '%' . $data['filter_username'] . '%');
}
if (isset($data['filter_board']) && $data['filter_board']) {
$query->where('b.title LIKE ?', '%' . $data['filter_board'] . '%');
}
if (isset($data['filter_board_id']) && !is_null($data['filter_board_id'])) {
$query->where('p.board_id = ?', (string) $data['filter_board_id']);
}
if (isset($data['filter_description']) && $data['filter_description']) {
$words = JO_Utf8::str_word_split(mb_strtolower($data['filter_description'], 'utf-8'), self::$searchWordLenght);
if (count($words) > 0) {
$sub = "SELECT `dic_id`, `dic_id` FROM `pins_dictionary` `d` WHERE ( ";
foreach ($words as $key => $word) {
if ($key) {
$sub .= ' OR ';
}
$sub .= "`d`.`word` = " . $db->quote($word) . " OR MATCH(`d`.`word`) AGAINST (" . $db->quote($word) . ")";
}
$sub .= ')';
$dicts = $db->fetchPairs($sub);
$tmp_dic_ids = array();
if (COUNT($dicts) > 0) {
$query->joinLeft('pins_invert', 'p.pin_id = pins_invert.pin_id', 'dic_id')->where('pins_invert.`dic_id` IN (' . implode(',', $dicts) . ')')->group('p.pin_id');
} else {
$query->where('p.pin_id = 0');
}
} else {
$query->where('p.pin_id = 0');
}
}
return $query;
}
/**
* @param JO_Db_Select $query
* @param array $data
* @return JO_Db_Select
*/
private static function FilterBuilder(JO_Db_Select $query, $data = array())
{
$db = JO_Db::getDefaultAdapter();
if (isset($data['filter_event_id']) && $data['filter_event_id']) {
$query->where('p.event_id = ?', (string) $data['filter_event_id']);
}
if (isset($data['filter_eventname']) && $data['filter_eventname']) {
$query->where('p.eventname LIKE ?', '%' . $data['filter_eventname'] . '%');
}
if (isset($data['filter_user_id']) && $data['filter_user_id']) {
$query->where('p.user_id = ?', (string) $data['filter_user_id']);
}
if (isset($data['filter_fullname']) && $data['filter_fullname']) {
$query->where('u.firstname LIKE ? OR u.lastname LIKE ?', '%' . $data['filter_fullname'] . '%');
}
if (isset($data['filter_username']) && $data['filter_username']) {
$query->where('u.username LIKE ?', '%' . $data['filter_username'] . '%');
}
if (isset($data['filter_description']) && $data['filter_description']) {
$query->where('p.description LIKE ?', '%' . $data['filter_description'] . '%');
}
return $query;
}
/**
* @return Ambigous <JO_Db_Select, JO_Db_Select>
*/
protected static function filterFriend(JO_Db_Select $query)
{
return $query->where('pins.user_id IN (SELECT user_id FROM users WHERE public = 1)');
}
/**
* Special handling for PDO query().
* All bind parameter names must begin with ':'
*
* @param string|JO_Db_Select $sql The SQL statement with placeholders.
* @param array $bind An array of data to bind to the placeholders.
* @return JO_Db_Statement_Pdo
* @throws JO_Db_Adapter_Exception To re-throw PDOException.
*/
public function query($sql, $bind = array())
{
if (empty($bind) && $sql instanceof JO_Db_Select) {
$bind = $sql->getBind();
}
if (is_array($bind)) {
foreach ($bind as $name => $value) {
if (!is_int($name) && !preg_match('/^:/', $name)) {
$newName = ":{$name}";
unset($bind[$name]);
$bind[$newName] = $value;
}
}
}
try {
return parent::query($sql, $bind);
} catch (PDOException $e) {
/**
* @see JO_Db_Statement_Exception
*/
require_once 'JO/Db/Statement/Exception.php';
throw new JO_Db_Statement_Exception($e->getMessage(), $e->getCode(), $e);
}
}
/**
* @return Ambigous <JO_Db_Select, JO_Db_Select>
*/
protected static function filterFriend(JO_Db_Select $query)
{
$db = JO_Db::getDefaultAdapter();
if (JO_Session::get('user[user_id]')) {
$has_pins = 'boards.user_id = ? OR boards.user_id IN (SELECT user_id FROM users WHERE public = 1)';
if (JO_Session::get('user[followers]')) {
$has_pins .= ' OR boards.user_id IN (' . $db->select()->from('users_following', 'user_id')->where('following_id = ?')->where('users_following.board_id = boards.board_id') . ')';
$has_pins .= ' OR boards.user_id IN (' . $db->select()->from('users_following_user', 'user_id')->where('following_id = ?') . ')';
}
$query->where(new JO_Db_Expr($has_pins), JO_Session::get('user[user_id]'));
if (JO_Session::get('user[followers]')) {
$query->where('boards.board_id NOT IN (SELECT board_id FROM `users_following_ignore` WHERE following_id = ?)', JO_Session::get('user[user_id]'));
}
} else {
$query->where('boards.user_id IN (SELECT user_id FROM users WHERE public = 1)');
}
return $query;
}
/**
* Performs a validation on the select query before passing back to the parent class.
* Ensures that only columns from the primary JO_Db_Table are returned in the result.
*
* @return string|null This object as a SELECT string (or null if a string cannot be produced)
*/
public function assemble()
{
$fields = $this->getPart(JO_Db_Table_Select::COLUMNS);
$primary = $this->_info[JO_Db_Table_Abstract::NAME];
$schema = $this->_info[JO_Db_Table_Abstract::SCHEMA];
if (count($this->_parts[self::UNION]) == 0) {
// If no fields are specified we assume all fields from primary table
if (!count($fields)) {
$this->from($primary, self::SQL_WILDCARD, $schema);
$fields = $this->getPart(JO_Db_Table_Select::COLUMNS);
}
$from = $this->getPart(JO_Db_Table_Select::FROM);
if ($this->_integrityCheck !== false) {
foreach ($fields as $columnEntry) {
list($table, $column) = $columnEntry;
// Check each column to ensure it only references the primary table
if ($column) {
if (!isset($from[$table]) || $from[$table]['tableName'] != $primary) {
require_once 'JO/Db/Table/Select/Exception.php';
throw new JO_Db_Table_Select_Exception('Select query cannot join with another table');
}
}
}
}
}
return parent::assemble();
}
/**
* @return Ambigous <JO_Db_Select, JO_Db_Select>
*/
protected static function filterFriend(JO_Db_Select $query)
{
$db = JO_Db::getDefaultAdapter();
if (JO_Session::get('user[user_id]')) {
$has_pins = 'users.user_id = ? OR users.public = 1';
if (JO_Session::get('user[followers]')) {
$has_pins .= ' OR users.user_id IN (' . $db->select()->from('users_following', 'user_id')->where('following_id = ?') . ')';
$has_pins .= ' OR users.user_id IN (' . $db->select()->from('users_following_user', 'user_id')->where('following_id = ?') . ')';
}
$query->where(new JO_Db_Expr($has_pins), JO_Session::get('user[user_id]'));
} else {
$query->where('users.public = 1');
}
return $query;
}
/**
* @param JO_Db_Select $query
* @param array $data
* @return JO_Db_Select
*/
private static function FilterBuilder(JO_Db_Select $query, $data = array())
{
$db = JO_Db::getDefaultAdapter();
$ignore_in = false;
$query->where('pins.store != ""');
if (isset($data['filter_likes']) && $data['filter_likes']) {
$ignore_in = true;
}
// if(isset($data['filter_likes']) && $data['filter_likes'] ) {
//// $query->where("pins.pin_id IN ( SELECT DISTINCT pin_id FROM pins_likes WHERE user_id = '".$data['filter_likes']."' )");
// $query->joinLeft('pins_likes', 'pins.pin_id = pins_likes.pin_id', array())
// ->where('pins_likes.user_id = ?', $data['filter_likes'])
// /*->group('pins.pin_id')*/;
// $ignore_in = true;
// }
if (isset($data['filter_pin_id']) && $data['filter_pin_id']) {
$query->where('pins.pin_id = ?', (string) $data['filter_pin_id']);
}
if (isset($data['filter_like_repin_comment']) && $data['filter_like_repin_comment'] === true) {
$query->where('pins.likes > 0')->where('pins.repins > 0')->where('pins.comments > 0');
}
if (isset($data['delete_request']) && $data['delete_request'] === true) {
$query->where('pins.delete_request = 1');
}
if (isset($data['filter_price_from']) && (int) $data['filter_price_from']) {
$query->where('pins.price >= ?', (int) $data['filter_price_from']);
$ignore_in = true;
} elseif (isset($data['allow_gifts'])) {
$query->where('pins.price > 0.0000');
$ignore_in = true;
}
if (isset($data['filter_id_in']) && $data['filter_id_in']) {
$query->where('pins.pin_id IN (?)', new JO_Db_Expr($data['filter_id_in']));
$ignore_in = true;
}
if (isset($data['filter_id_not']) && $data['filter_id_not']) {
$query->where('pins.pin_id NOT IN (?)', new JO_Db_Expr($data['filter_id_not']));
$ignore_in = true;
}
if (isset($data['filter_price_to']) && (int) $data['filter_price_to']) {
$query->where('pins.price <= ?', (int) $data['filter_price_to']);
$ignore_in = true;
}
if (isset($data['filter_marker']) && (string) $data['filter_marker']) {
$query->where('pins.pin_id <= ?', (string) $data['filter_marker']);
$ignore_in = true;
}
if (isset($data['filter_repin_from']) && !is_null($data['filter_repin_from'])) {
$query->where('pins.repin_from = ?', (string) $data['filter_repin_from']);
$ignore_in = true;
}
if (isset($data['filter_source_id']) && !is_null($data['filter_source_id'])) {
$query->where('pins.source_id = ?', (string) $data['filter_source_id']);
$ignore_in = true;
}
if (isset($data['filter_from']) && !is_null($data['filter_from'])) {
$query->where('pins.from = ?', $data['filter_from']);
$ignore_in = true;
}
if (isset($data['filter_from_md5']) && !is_null($data['filter_from_md5'])) {
$query->where('pins.from_md5 = ?', $data['filter_from_md5']);
$ignore_in = true;
}
if (isset($data['filter_category_id']) && !is_null($data['filter_category_id'])) {
$query->where('pins.category_id = ?', (string) $data['filter_category_id']);
$ignore_in = true;
}
if (isset($data['filter_is_video']) && !is_null($data['filter_is_video'])) {
$query->where('pins.is_video = ?', (int) $data['filter_is_video']);
$ignore_in = true;
}
if (isset($data['filter_board_id']) && !is_null($data['filter_board_id'])) {
$query->where('pins.board_id = ?', (string) $data['filter_board_id']);
$ignore_in = true;
}
// if(isset($data['filter_ub_id']) && !is_null($data['filter_ub_id'])) {
// $query->where('pins.ub_id = ?', (string)$data['filter_ub_id']);
// $ignore_in = true;
// }
if (isset($data['filter_user_id']) && !is_null($data['filter_user_id'])) {
$query->where('pins.user_id = ?', (string) $data['filter_user_id']);
$ignore_in = true;
}
if (isset($data['filter_description'])) {
$words = JO_Utf8::str_word_split(mb_strtolower($data['filter_description'], 'utf-8'), self::$searchWordLenght);
if (count($words) > 0) {
/*$sub = "SELECT `i`.`pin_id` FROM `pins_invert` `i`, `pins_dictionary` `d` WHERE `i`.`dic_id` = `d`.`dic_id` AND ( ";
foreach($words AS $key => $word) {
if($key) {
$sub .= ' OR ';
}
$sub .= "`d`.`word` = " . $db->quote($word) . " OR MATCH(`d`.`word`) AGAINST (" . $db->quote($word) . ")";
}
$sub .= ')';
$query->where('pins.pin_id IN (' . $sub . ')');
$sub = "SELECT `dic_id`, `dic_id` FROM `pins_dictionary` `d` WHERE ( ";
foreach($words AS $key => $word) {
if($key) {
$sub .= ' OR ';
}
$sub .= "`d`.`word` = " . $db->quote($word) . " OR MATCH(`d`.`word`) AGAINST (" . $db->quote($word) . ")";
}
$sub .= ')';
$dicts = $db->fetchPairs($sub);
$tmp_dic_ids = array();
if(COUNT($dicts) > 0) {
$pins = $db->fetchPairs("SELECT `pin_id`, `pin_id` FROM `pins_invert` `i` WHERE `i`.`dic_id` IN (" . implode(',', $dicts) . ")");
if(count($pins) > 0) {
$query->where('pins.pin_id IN (' . implode(',', $pins) . ')');
} else {
$query->where('pins.pin_id = 0');
}
} else {
$query->where('pins.pin_id = 0');
}*/
$sub = "SELECT `dic_id`, `dic_id` FROM `pins_dictionary` `d` WHERE ( ";
foreach ($words as $key => $word) {
if ($key) {
$sub .= ' OR ';
}
$sub .= "`d`.`word` = " . $db->quote($word) . " OR MATCH(`d`.`word`) AGAINST (" . $db->quote($word) . ")";
}
$sub .= ')';
$dicts = $db->fetchPairs($sub);
$tmp_dic_ids = array();
if (COUNT($dicts) > 0) {
// $pins = $db->fetchPairs("SELECT `pin_id`, `pin_id` FROM `pins_invert` `i` WHERE `i`.`dic_id` IN (" . implode(',', $dicts) . ")");
//$query->where('pins.pin_id IN ( SELECT DISTINCT `pin_id` FROM `pins_invert` `i` WHERE `i`.`dic_id` IN (' . implode(',', $dicts) . ') )');
$query->joinLeft('pins_invert', 'pins.pin_id = pins_invert.pin_id', 'dic_id')->where('pins_invert.`dic_id` IN (' . implode(',', $dicts) . ')')->group('pins.pin_id');
$ignore_in = true;
} else {
$query->where('pins.pin_id = 0');
}
} else {
$query->where('pins.pin_id = 0');
}
}
if (isset($data['following_users_from_user_id']) && (string) $data['following_users_from_user_id']) {
if (JO_Session::get('user[following]')) {
$sql1 = "SELECT following_id FROM users_following_user WHERE user_id = ?";
$sql = "SELECT p.pin_id FROM `users_following` a, pins p WHERE a.following_id NOT IN(" . $sql1 . ") AND a.`board_id` = p.`board_id`";
$query->where("((pins.pin_id IN (" . $sql . ") OR pins.user_id IN (" . $sql1 . ")) AND (`board_id` NOT IN (SELECT board_id FROM `users_following_ignore` WHERE following_id IN(" . $sql1 . ") AND user_id = ?))) OR pins.user_id = ?", (string) JO_Session::get('user[user_id]'));
}
/*$sql1 = "SELECT following_id FROM users_following_user WHERE user_id = ?";
// $sql = "SELECT p.pin_id FROM `users_following` a, `users_boards` b, pins p WHERE
// a.following_id NOT IN(" . $sql1 . ") AND a.`board_id`=b.`board_id` AND b.`public`=1 AND a.`board_id` = p.`board_id` AND
// (p.pin_id IN (SELECT pin_id FROM pins WHERE public = 1) OR p.user_id=".(string)JO_Session::get('user[user_id]').")";
// $sql = "SELECT p.pin_id FROM `users_following` a, `users_following_ignore` c, `boards` b, pins p WHERE
// a.following_id NOT IN(" . $sql1 . ") AND a.`board_id`=b.`board_id` AND b.`public`=1 AND a.`board_id` = p.`board_id` AND
// (p.pin_id IN (SELECT pin_id FROM pins WHERE public = 1) OR p.user_id=?)";
$sql = "SELECT p.pin_id FROM `users_following` a, pins p WHERE a.following_id NOT IN(" . $sql1 . ") AND a.`board_id` = p.`board_id`";
$query->where("((pins.pin_id IN (" . $sql . ") OR pins.user_id IN (" . $sql1 . ")) AND (`board_id` NOT IN (SELECT board_id FROM `users_following_ignore` WHERE following_id IN(" . $sql1 . ") AND user_id = ?))) OR pins.user_id = ?", (string)JO_Session::get('user[user_id]'));
// $query->where("pins.board_id IN (SELECT board_id FROM users_following WHERE user_id = '" . (string)$data['following_users_from_user_id'] . "')");
//echo $query; exit;
/*$sql = "SELECT board_id FROM users_boards WHERE (
user_id IN (
SELECT following_id FROM users_following_user WHERE user_id = ?
)
OR
board_id IN (
SELECT board_id FROM users_following WHERE user_id = ?
)
)
AND
board_id NOT IN (
SELECT board_id FROM users_following_ignore WHERE user_id = ?
)";
$query->where("pins.board_id IN (" . $sql . ") OR pins.user_id = ?", (string)JO_Session::get('user[user_id]'));
*/
} else {
// if($ignore_in) {
// if((string)JO_Session::get('user[user_id]')) {
// $query->where("(pins.public = 1 OR user_id = ".(string)JO_Session::get('user[user_id]').")");
// } else {
// $query->where("pins.public = 1");
// }
// } else {
// if((string)JO_Session::get('user[user_id]')) {
// $query->where("(pins.pin_id IN (SELECT pin_id FROM pins WHERE public = 1)) OR (pins.user_id IN (SELECT DISTINCT user_id FROM pins WHERE user_id = ".(string)JO_Session::get('user[user_id]')."))");
// } else {
// $query->where("pins.pin_id IN (SELECT pin_id FROM pins WHERE public = 1)");
// }
// }
}
// echo $query; exit;
return $query;
}
