De DB functies

[Bart]

identity(){
FROM ads
WHERE tags == query(SELECT tags FROM publisher WHERE property = Referrer )
}

[Bart]

SELECT ads.ad_id FROM ads JOIN tags ON tags.ad_id=ads.ad_id WHERE
	tags = (SELECT tags FROM publisher WHERE property = Referrer )

foreign table publisher needs to be created

[Bart]

Now Interesting needs to select from.. ads based on identity... Like, lots of ad candidates.. maybe summarize by count(ad_id) as identity per ad_id? Then.. highest number of keyword matches wins? ORDER BY identity desc LIMIT 1

[Bart]

Uch..

SELECT ads.ad_id, count(*)/(SELECT count(tags) FROM tags WHERE tags.ad_id = ads.ad_id) AS interest FROM ads
JOIN tags ON tags.ad_id = ads.ad_id WHERE ads.crude < crude AND charity = s AND tags = (SELECT tags FROM publisher WHERE property = referrer)
GROUP BY ads.ad_id ORDER BY interest DESC

Had ik tot nu

[Bart]

Maar die count(*)/count(<tags op de ad>) werkt niet

Dat moet ook count(*)/(SELECT count(tags) ... ) zijn

[Bart]

DECLARE
	ad ad_link := interesting(crude, advertising_id, charity, referrer);
BEGIN
	INSERT INTO l (GUID, Forward) VALUES (link_id, ad.link);
	RETURN ad.image_link;
END;

[Bart]

SELECT ads.link, ads.image_link from ads WHERE ad_id = (SELECT ad_id FROM public.identity(advertising_id, crude, charity) LIMIT 1)

[Bart]

Nu ga ik in zowel de interesting() als identity() de ads tabel in, in identity zelfs met een join op tags, en een select in een where op alle tags in publisher.

Ik weet niet wat dit doet qua performance. Maar een *noot*, de ads tabel 'schoon' houden, en alleen lopende campagnes in hebben staan zal helpen.

[Bart]

En, ik verwerk de referrer nog niet naar een tabel om te crawlen.. Ga ik dat doen? <meta keywords>?