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