/** * @param array $categoriesObj * @param array $status * * @return array */ public function getLastPublishedByCat($categoriesObj, $status = array(_PUBLISHER_STATUS_PUBLISHED)) { $ret = array(); $catIds = array(); /* @var $category PublisherCategory */ foreach ($categoriesObj as $parentid) { foreach ($parentid as $category) { $catId = $category->getVar('categoryid'); $catIds[$catId] = $catId; } } if (empty($catIds)) { return $ret; } // $sql = "SELECT mi.categoryid, mi.itemid, mi.title, mi.short_url, mi.uid, mi.datesub"; // $sql .= " FROM (SELECT categoryid, MAX(datesub) AS date FROM " . $this->db->prefix('publisher_items'); // $sql .= " WHERE status IN (" . implode(',', $status) . ")"; // $sql .= " AND categoryid IN (" . implode(',', $catIds) . ")"; // $sql .= " GROUP BY categoryid)mo"; // $sql .= " JOIN " . $this->db->prefix('publisher_items') . " mi ON mi.datesub = mo.date"; $qb = $this->db2->createXoopsQueryBuilder(); $qb->select('mi.categoryid', 'mi.itemid', 'mi.title', 'mi.short_url', 'mi.uid', 'mi.datesub'); $subqb = $this->db2->createXoopsQueryBuilder(); $subqb->select('categoryid', 'MAX(datesub) AS date')->fromPrefix('publisher_items', '')->where($subqb->expr()->in('status', $status))->andWhere($subqb->expr()->in('categoryid', $catIds))->groupBy('categoryid'); $subquery = '(' . $subqb->getSQL() . ')'; $qb->from($subquery, 'mo')->joinPrefix('mo', 'publisher_items', 'mi', 'mi.datesub = mo.date'); $result = $qb->execute(); while ($row = $result->fetch(\PDO::FETCH_ASSOC)) { $item = new PublisherItem(); $item->assignVars($row); $ret[$row['categoryid']] = $item; unset($item); } return $ret; }
/** * @param array $categoriesObj * @param array $status * * @return array */ public function getLastPublishedByCat($categoriesObj, $status = array(_PUBLISHER_STATUS_PUBLISHED)) { $ret = array(); $catIds = array(); foreach ($categoriesObj as $parentid) { foreach ($parentid as $category) { $catId = $category->getVar('categoryid'); $catIds[$catId] = $catId; } } if (empty($catIds)) { return $ret; } /*$cat = array(); $sql = "SELECT categoryid, MAX(datesub) as date FROM " . $this->db->prefix('publisher_items') . " WHERE status IN (" . implode(',', $status) . ") GROUP BY categoryid"; $result = $this->db->query($sql); while ($row = $this->db->fetchArray($result)) { $cat[$row['categoryid']] = $row['date']; } if (count($cat) == 0) return $ret; $sql = "SELECT categoryid, itemid, title, short_url, uid, datesub FROM " . $this->db->prefix('publisher_items'); $criteriaBig = new CriteriaCompo(); foreach ($cat as $id => $date) { $criteria = new CriteriaCompo(new Criteria('categoryid', $id)); $criteria->add(new Criteria('datesub', $date)); $criteriaBig->add($criteria, 'OR'); unset($criteria); } $sql .= " " . $criteriaBig->renderWhere(); $result = $this->db->query($sql); while ($row = $this->db->fetchArray($result)) { $item = new PublisherItem(); $item->assignVars($row); $ret[$row['categoryid']] = $item; unset($item); } */ $sql = "SELECT mi.categoryid, mi.itemid, mi.title, mi.short_url, mi.uid, mi.datesub"; $sql .= " FROM (SELECT categoryid, MAX(datesub) AS date FROM " . $this->db->prefix('publisher_items'); $sql .= " WHERE status IN (" . implode(',', $status) . ")"; $sql .= " AND categoryid IN (" . implode(',', $catIds) . ")"; $sql .= " GROUP BY categoryid)mo"; $sql .= " JOIN " . $this->db->prefix('publisher_items') . " mi ON mi.datesub = mo.date"; $result = $this->db->query($sql); while ($row = $this->db->fetchArray($result)) { $item = new PublisherItem(); $item->assignVars($row); $ret[$row['categoryid']] = $item; unset($item); } return $ret; }
/** * @param array $status * * @return array */ public function getLastPublishedByCat($status = array(_PUBLISHER_STATUS_PUBLISHED)) { $ret = array(); $cat = array(); $sql = "SELECT categoryid, MAX(datesub) as date FROM " . $this->db->prefix('publisher_items') . " WHERE status IN (" . implode(',', $status) . ") GROUP BY categoryid"; $result = $this->db->query($sql); while ($row = $this->db->fetchArray($result)) { $cat[$row['categoryid']] = $row['date']; } if (count($cat) == 0) { return $ret; } $sql = "SELECT categoryid, itemid, title, short_url, uid, datesub FROM " . $this->db->prefix('publisher_items'); $criteriaBig = new CriteriaCompo(); foreach ($cat as $id => $date) { $criteria = new CriteriaCompo(new Criteria('categoryid', $id)); $criteria->add(new Criteria('datesub', $date)); $criteriaBig->add($criteria, 'OR'); unset($criteria); } $sql .= " " . $criteriaBig->renderWhere(); $result = $this->db->query($sql); while ($row = $this->db->fetchArray($result)) { $item = new PublisherItem(); $item->assignVars($row); $ret[$row['categoryid']] = $item; unset($item); } return $ret; }