| 1 |
localhost.bardcanvas_demo |
select
`categories`.*,
(
select
slug
from
categories c2
where
c2.id_category = categories.parent_category
) as parent_category_slug,
(
select
title
from
categories c2
where
c2.id_category = categories.parent_category
) as parent_category_title,
(
select
group_concat(
concat(c2.slug, ' ', c2.title) separator '
'
)
from
categories c2
where
c2.parent_category = categories.id_category
and (c2.visibility = 'public')
) as child_categories_list,
(
select
count(p.id_post)
from
posts p
where
p.main_category = categories.id_category
and p.status = 'published'
and p.visibility = 'public'
) as posts_count,
(
select
count(c.id_comment)
from
comments c,
posts p
where
c.status = 'published'
and c.id_post = p.id_post
and p.main_category = categories.id_category
and p.status = 'published'
and p.visibility = 'public'
) as comments_count
from
`categories`
where
`parent_category` = 'C11499905917613'
and visibility = 'public'
order by
title |
3 |
2.303 |
| 3 |
localhost.bardcanvas_demo |
select
posts.id_post,
posts.parent_post,
posts.id_author,
posts.slug,
posts.title,
posts.excerpt,
posts.content,
posts.main_category,
posts.visibility,
posts.status,
posts.password,
posts.allow_comments,
posts.pin_to_home,
posts.pin_to_main_category_index,
posts.creation_date,
posts.creation_ip,
posts.creation_host,
posts.creation_location,
posts.publishing_date,
posts.expiration_date,
posts.comments_count,
posts.last_update,
posts.id_featured_image,
(
select
views
from
post_views
where
post_views.id_post = posts.id_post
) as views,
(
select
last_viewed
from
post_views
where
post_views.id_post = posts.id_post
) as last_viewed,
(
select
concat(
user_name, '\t', display_name, '\t',
email, '\t', level
)
from
account
where
account.id_account = posts.id_author
) as _author_data,
(
select
concat(
slug, '\t', title, '\t', visibility,
'\t', min_level
)
from
categories
where
categories.id_category = posts.main_category
) as _main_category_data,
(
select
path
from
media
where
media.id_media = posts.id_featured_image
) as featured_image_path,
(
select
thumbnail
from
media
where
media.id_media = posts.id_featured_image
) as featured_image_thumbnail,
(
select
type
from
media
where
media.id_media = posts.id_featured_image
) as featured_media_type,
(
select
group_concat(
tag
order by
date_attached asc,
order_attached asc separator ','
)
from
post_tags
where
post_tags.id_post = posts.id_post
) as tags_list,
(
select
group_concat(
id_media
order by
date_attached asc,
order_attached asc separator ','
)
from
post_media
where
post_media.id_post = posts.id_post
) as media_list,
(
select
group_concat(
id_category
order by
date_attached asc,
order_attached asc separator ','
)
from
post_categories
where
post_categories.id_post = posts.id_post
) as categories_list,
(
select
group_concat(
id_account
order by
date_attached asc,
order_attached asc separator ','
)
from
post_mentions
where
post_mentions.id_post = posts.id_post
) as mentions_list,
(
select
title
from
posts p2
where
p2.id_post = posts.parent_post
) as parent_post_title,
(
select
slug
from
posts p2
where
p2.id_post = posts.parent_post
) as parent_post_slug,
(
select
count(id_post)
from
posts p2
where
p2.parent_post = posts.id_post
) as children_count,
(
select
c.creation_date
from
comments c
where
c.id_post = posts.id_post
and c.status = 'published'
order by
c.creation_date desc
limit
1
) as last_commented
from
`posts`
where
status = 'published'
and visibility <> 'private'
and publishing_date <= '2018-09-25 01:03:06'
and (
expiration_date = '0000-00-00 00:00:00'
or expiration_date > '2018-09-25 01:03:06'
)
and visibility = 'public'
and (
posts.main_category in (
select
id_category
from
categories
where
visibility = 'public'
or visibility = 'users'
or (
visibility = 'level_based'
and '1' >= min_level
)
)
)
and (
main_category = 'C11499905917613'
or id_post in (
select
id_post
from
post_categories
where
post_categories.id_category = 'C11499905917613'
)
)
order by
pin_to_main_category_index desc,
publishing_date desc
limit
30 offset 0 |
0 |
1.582 |
| 5 |
localhost.bardcanvas_demo |
select
posts.id_post,
posts.parent_post,
posts.id_author,
posts.slug,
posts.title,
posts.excerpt,
posts.content,
posts.main_category,
posts.visibility,
posts.status,
posts.password,
posts.allow_comments,
posts.pin_to_home,
posts.pin_to_main_category_index,
posts.creation_date,
posts.creation_ip,
posts.creation_host,
posts.creation_location,
posts.publishing_date,
posts.expiration_date,
posts.comments_count,
posts.last_update,
posts.id_featured_image,
(
select
views
from
post_views
where
post_views.id_post = posts.id_post
) as views,
(
select
last_viewed
from
post_views
where
post_views.id_post = posts.id_post
) as last_viewed,
(
select
concat(
user_name, '\t', display_name, '\t',
email, '\t', level
)
from
account
where
account.id_account = posts.id_author
) as _author_data,
(
select
concat(
slug, '\t', title, '\t', visibility,
'\t', min_level
)
from
categories
where
categories.id_category = posts.main_category
) as _main_category_data,
(
select
path
from
media
where
media.id_media = posts.id_featured_image
) as featured_image_path,
(
select
thumbnail
from
media
where
media.id_media = posts.id_featured_image
) as featured_image_thumbnail,
(
select
type
from
media
where
media.id_media = posts.id_featured_image
) as featured_media_type,
(
select
group_concat(
tag
order by
date_attached asc,
order_attached asc separator ','
)
from
post_tags
where
post_tags.id_post = posts.id_post
) as tags_list,
(
select
group_concat(
id_media
order by
date_attached asc,
order_attached asc separator ','
)
from
post_media
where
post_media.id_post = posts.id_post
) as media_list,
(
select
group_concat(
id_category
order by
date_attached asc,
order_attached asc separator ','
)
from
post_categories
where
post_categories.id_post = posts.id_post
) as categories_list,
(
select
group_concat(
id_account
order by
date_attached asc,
order_attached asc separator ','
)
from
post_mentions
where
post_mentions.id_post = posts.id_post
) as mentions_list,
(
select
title
from
posts p2
where
p2.id_post = posts.parent_post
) as parent_post_title,
(
select
slug
from
posts p2
where
p2.id_post = posts.parent_post
) as parent_post_slug,
(
select
count(id_post)
from
posts p2
where
p2.parent_post = posts.id_post
) as children_count,
(
select
c.creation_date
from
comments c
where
c.id_post = posts.id_post
and c.status = 'published'
order by
c.creation_date desc
limit
1
) as last_commented
from
`posts`
where
id_post in ('5011536323476094') |
1 |
1.283 |
