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_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; }
/** * @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; }
/** * @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)'); }
/** * @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; }
/** * @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; }