[wp-hackers] $wpdb->prefix

[Xen]

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