2. и sphinx

Post Reply
serge
Posts: 888
Joined: Mon Mar 14, 2011 7:53 am

2. и sphinx

Post by serge »

после конверта 1. в 2.

стокнулся с тем, что надо переписать конфиг сфинкса, в первой версии он выглядел так:
sql_query = SELECT id, crc32(g.gallery_md5) as gallery_md5_crc, UNIX_TIMESTAMP(activation_date) as date, tags, alt, gd.description, duration, g.sponsor_id, rgroup, content_type, gs.total_ctr, sponsor_name, sponsor_site FROM rot_galleries as g \
JOIN rot_gallery_stats AS gs ON g.id = gs.thumb_id \
JOIN rot_gallery_info AS gi ON g.gallery_md5 = gi.gallery_md5 \
JOIN rot_gallery_data AS gd ON g.gallery_md5 = gd.gallery_md5 \
JOIN rot_sponsors AS sp ON sp.sponsor_id = g.sponsor_id \
WHERE g.status = 1 and gs.best_thumb = 'yes' and rgroup != 0
sql_attr_timestamp = date
sql_attr_uint = duration
sql_attr_uint = sponsor_id
sql_attr_uint = rgroup
sql_attr_float = total_ctr
sql_attr_uint = content_type
sql_attr_uint = gallery_md5_crc

sql_query_info = SELECT * FROM rot_galleries WHERE id=$id
во второй версии по мануалу сделал его таким:

Code: Select all

	sql_query_pre = SET NAMES utf8
	sql_query				= SELECT gi.gallery_id, UNIX_TIMESTAMP(gi.activation_date) as date, alt, description, gi.duration, sponsor_id, gs.total_ctr, gi.content_type, \
							(SELECT group_concat(tag_name) FROM rot_gal2tag g2t \
							LEFT JOIN rot_tags as t on t.tag_id = g2t.tag_id \
							WHERE g2t.gallery_id = gi.gallery_id) as tags, \
							(SELECT group_concat(tag_id) FROM rot_gal2tag g2t \
							WHERE g2t.gallery_id = gi.gallery_id) as tag_ids, \
							(SELECT group_concat(gss.group_id) FROM rot_gallery_stats1 as gss \
							WHERE gss.gallery_id = gi.gallery_id AND group_id != 0) as categories \
							FROM rot_gallery_info AS gi \
							JOIN rot_gallery_data1 AS gd ON gi.gallery_id = gd.gallery_id \
							JOIN rot_gallery_stats1 AS gs ON gs.gallery_id = gi.gallery_id \
							WHERE gallery_status = 'active' and gallery_type = 0 \
							and gs.best_thumb = 'yes' and gs.group_id = 0


	sql_attr_timestamp		= date
	sql_attr_uint		    = duration
	sql_attr_uint		    = sponsor_id
	sql_attr_float		    = total_ctr
	sql_attr_uint		    = content_type
    sql_attr_multi 			= uint categories from field; 
    sql_attr_multi 			= uint tag_ids from field; 
Проблема в том, что у меня в первой версии было кастомно добавлено в базу: sponsor_name, sponsor_site. Подскажите, пожалуйста, как должен выглядеть конфиг для второй версии с возможностью поиска по сайтам спонсора и его названию.

Спасибо.
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: 2. и sphinx

Post by admin »

Добавил в сетинги что б генерило код со спонами

В целом он такой

Code: Select all

SELECT gi.gallery_id, UNIX_TIMESTAMP(gi.activation_date) as date, gd.alt, gd.description, gi.duration, gi.sponsor_id, gs.total_ctr, gi.content_type, 
rot_sponsors.sponsor_name, rot_sponsors.sponsor_site,
(SELECT group_concat(tag_name) FROM rot_gal2tag g2t 
LEFT JOIN rot_tags as t on t.tag_id = g2t.tag_id 
WHERE g2t.gallery_id = gi.gallery_id) as tags, 
(SELECT group_concat(tag_id) FROM rot_gal2tag g2t 
WHERE g2t.gallery_id = gi.gallery_id) as tag_ids, 
(SELECT group_concat(gss.group_id) FROM rot_gallery_stats1 as gss 
WHERE gss.gallery_id = gi.gallery_id AND group_id != 0) as categories 
FROM rot_gallery_info AS gi 
JOIN rot_sponsors on rot_sponsors.sponsor_id = gi.sponsor_id
JOIN rot_gallery_data1 AS gd ON gi.gallery_id = gd.gallery_id 
JOIN rot_gallery_stats1 AS gs ON gs.gallery_id = gi.gallery_id 
WHERE gallery_status = 'active' and gallery_type = 0 
and gs.best_thumb = 'yes' and gs.group_id = 0
Don't forget to run script update
serge
Posts: 888
Joined: Mon Mar 14, 2011 7:53 am

Re: 2. и sphinx

Post by serge »

скопировал из сеттингов:

Code: Select all

	sql_query_pre = SET NAMES utf8
	sql_query_pre = UPDATE rot_settings SET value = (SELECT MAX(gallery_id) FROM rot_gallery_info) WHERE name = 'sphinx_max_gallery_id'

	sql_query	= SELECT gi.gallery_id, UNIX_TIMESTAMP(gi.activation_date) as date, \
	gd.alt, gd.description, gi.duration, gi.sponsor_id, gs.total_ctr, gi.content_type, rot_sponsors.sponsor_name, rot_sponsors.sponsor_site,\
	(SELECT group_concat(tag_id) FROM rot_gal2tag g2t                               \
	WHERE g2t.gallery_id = gi.gallery_id) as tag_ids,                               \
	(SELECT group_concat(gss.group_id) FROM rot_gallery_stats1 as gss               \
	WHERE gss.gallery_id = gi.gallery_id AND group_id != 0) as categories           \
	FROM rot_gallery_info AS gi                                                     \
	JOIN rot_sponsors on rot_sponsors.sponsor_id = gi.sponsor_id                    \
	JOIN rot_gallery_data1 AS gd ON gi.gallery_id = gd.gallery_id                   \
	JOIN rot_gallery_stats1 AS gs ON gs.gallery_id = gi.gallery_id                  \
	WHERE gallery_status = 'active' and gallery_type = 0                            \
	and gs.best_thumb = 'yes' and gs.group_id = 0                                   \
	AND gs.group_id NOT IN (SELECT group_id FROM rot_groups_data WHERE status != 1) 


	sql_attr_timestamp		= date
	sql_attr_uint		    = duration
	sql_attr_uint		    = sponsor_id
	sql_attr_float		    = total_ctr
	sql_attr_uint		    = content_type
    sql_attr_multi 			= uint tag_ids from field; 
    sql_attr_multi 			= uint categories from field; 

переиндексировал... по имени сайта спаонсора результатов не выдает
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: 2. и sphinx

Post by admin »

не перезапущен сфинкс или что-то такое
проверить оч прото - запускаем запрос в мускле - видны данные сайта спона?
если да то смотрим в шеле запрос в сфинкс ? видно ?

как вдино до этого момента скрипт не задействован вообще.
Don't forget to run script update
serge
Posts: 888
Joined: Mon Mar 14, 2011 7:53 am

Re: 2. и sphinx

Post by serge »

целый день ковыряем - результат нулевой. Либо админы тупят, либо там просто в базу сфинкса не передается значение с именем сайта спонсора и поэтому ноль результатов. Хотел бы отметить, что сайт был сконвертирован из 1-ой версии, возможно там что-то иначе передалось и поэтому такой косяк...
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: 2. и sphinx

Post by admin »

Если просто запрос который выше сделать в мускле - видно как выдает нужные данные?
Don't forget to run script update
Post Reply