[wp-hackers] $wpdb->prefix

[Xen]

Slight error in this SQL:


SELECT ... FROM ... AS p JOIN (prefix_term_relationships AS, 
prefix_term_taxonomy AS tax, prefix_terms AS term) ON (p.ID = 
r.object_id AND r.term_taxonomy_id = tax.term_taxonomy_id AND 
tax.term_id = term.term_ID)


Fixed.


On 14-06-15 09:29, Xen wrote:
> Yes you can apply a few joins.
>
> The database structure is fairly easily set up. I believe you need to 
> do two joins to marry the terms with the term_taxonomy (terms contains 
> the name and the slug, term_taxonomy the description and the 
> term_taxonomy_id (which is usually the same) which is then used in the 
> ...) and the term_relationships.
>
> In term_relationships you find the taxonomy_id coupled to post_ids 
> (object_id).
>
> So your JOIN will be something of the kind that:
>
> SELECT ... FROM ... AS p JOIN prefix_term_relationships AS r ON p.ID = 
> r.object_id JOIN prefix_term_taxonomy AS tax ON r.term_taxonomy_id = 
> tax.term_taxonomy_id JOIN prefix_terms AS term ON tax.term_id = 
> term.term_ID
>
> That's three.
>
> In your statement you then have access to term.slug (for example) or 
> tax.taxonomy (to differentiate tags and categories).
>
> You can probably write this nicer with
>
> SELECT ... FROM ... AS p JOIN (prefix_term_relationships AS, 
> prefix_term_taxonomy AS tax, prefix_terms AS term) ON (r ON p.ID = 
> r.object_id AND r.term_taxonomy_id = tax.term_taxonomy_id AND 
> tax.term_id = term.term_ID)
>
> If you need extra material from the other tables, apart from just a 
> WHERE check, you may need to make an inner statement:
>
> SELECT .., ..., .. FROM ( ... )
>
> but I'm not sure about that, I'm sure you can just supply:
>
> SELECT p.ID, term.slug FROM ..... JOIN ( .... ) ON ( ... )
>
> Good luck.
>
> Bart.
>
>
> On 13-06-15 22:27, Haluk Karamete wrote:
>> Actually using wpdb->get_results could have just done the job for me 
>> ( but
>> I need a more complex SQL that a simple one as follows )
>>
>>   $SQL = "
>>      SELECT other_prefix_posts.*
>>      FROM other_prefix_posts
>>      WHERE ( other_prefix_posts.ID < 10 )
>>      AND other_prefix_posts.post_status = 'publish'
>>      AND other_prefix_posts.post_type = 'post'
>>      ORDER BY other_prefix_posts.post_date DESC
>>   ";
>>
>>   $posts = $wpdb->get_results($querystr, OBJECT);
>>
>> This won't cut my need cause I need post_tags and category terms 
>> sprinkled
>> over this SQL & that's where the buck stops.
>>
>>
>>
>>
>>
>>
>>
>> On Sat, Jun 13, 2015 at 9:02 AM, Stephen Harris 
>> <contact at stephenharris.info>
>> wrote:
>>
>>> This method isn't without it's difficulties, because of caching and
>>> globals and the like.
>>>
>>> You could attempt to mimic WordPress' switch_to_bog() adapted for a
>>> non-multisite instance. E.g. flush cache, reinitialise use roles 
>>> etc. This
>>> would involve flushing the cache completely
>>> ($wp_object_cache->switch_to_blog()) won't work here because you're 
>>> not in
>>> a multisite environment).
>>>
>>> But I'm not convinced it would be plain sailing from there either :).
>>>
>>> Depending on what is required, simply reading the first site's RSS feed
>>> and rendering that might the most straightforward solution.
>>>
>>> Stephen
>>>
>>>
>>> On 13/06/15 14:04, J.D. Grimes wrote:
>>>
>>>> Depending on what you are doing, you might not want or need 
>>>> multisite. In
>>>> that case, this should work:
>>>>
>>>>
>>>>
>>>> $old_prefix = $wpdb->set_prefix( $site_1_prefix );
>>>>
>>>> // get posts, etc., here.
>>>>
>>>> $wpdb->set_prefix( $old_prefix );
>>>>
>>>>
>>>>
>>>> See wpdb::set_prefix():
>>>> https://developer.wordpress.org/reference/classes/wpdb/set_prefix/
>>>>
>>>> -J.D.
>>>>
>>> _______________________________________________
>>> wp-hackers mailing list
>>> wp-hackers at lists.automattic.com
>>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>>
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers