How do I join these tables?

I'm trying to import WordPress users and usermeta tables to my Laravel DB. So far, I've established connection to the WP database and now struggling to find the right way to join these tables. The table structure is as follows -

wp_users : Only listing required columns

ID | user_email 
1 | [email protected]
2 | [email protected]
...

wp_usermeta : Only listing required columns

umeta_id | user_id | meta_key | meta_value

1 | 1 | first_name | John
2 | 1 | last_name | Travolta
3 | 1 | city | Phucket
4 | 2 | first_name| Patrick
5 | 2 | city | London

and so on. My aim is to get a result where I've all the user information available for importing, row-wise.

$builder = DB::connection('wp')->table('wp_users')->join('wp_usermeta', 'ID', '=', 'wp_usermeta.user_id')->select('*')->limit(500);

that's been my best attempt so far; but it's not giveing me the results I desire. Would really appreciate if someone could show me the right direction. Thanks!

bobbybouwmann

8 years ago

Level 88

Well a join is always the same and it's also documented in the Laravel documentation. Something like this should do

$builder = DB::connection('wp')->table('wp_users')
    ->join('wp_usermeta', 'wp_users.ID', '=', 'wp_usermeta.user_id')
    ->limit(500):

By default it will select all columns.

Let me know if this helps you any further!

thebigk

8 years ago

Level 13

@bobbybouwmann - But won't that stop at the first row of wp_usermeta? I need a way to select all meta-keys associated with any user ID. For example,

ID | user_email | first_name | last_name ... kinda like that? Not sure if this is even possible.

Update:

This returns 500 objects as follows:

[0] -> [ID = 1, meta_key = 'first_name', meta_value = John]
[1] -> [ID = 1, meta_key= last_name, meta_value = Travloa ]
....

and so on. Well it's closer to what I'm expecting, but not what I'd really want.

bobbybouwmann

8 years ago

Level 88

Well the database structure of WordPress is designed so it can store any data. From there you probably should combine everything to one group. You can do something like this

// This is a users collection
$users = DB::connection('wp')->table('wp_users')
    ->join('wp_usermeta', 'wp_users.ID', '=', 'wp_usermeta.user_id')
    ->get();

$combinedUsers = $users->groupBy('ID');

Documentation: https://laravel.com/docs/5.6/collections#method-groupby You can't do this in your query, because all the columns are the same so if you would group there it would override the column. I think this is your best shot in doing this.

Let me know if that works for you!

1 like

thebigk

8 years ago

Level 13

@bobbybouwmann - Yeah; but without limiting, the query seems to be taking forever on about ~40K records in users table and ~7,50,000 records in usermeta. Not sure if limit(x') would cause issues.

bobbybouwmann

8 years ago

Best Answer

Level 88

Well if you use limit, you might get some users and not all of there information. You can also do something like this

$users = DB::connection('wp')->table('wp_users')->get();

foreach($users->chunk(10) as $partOfUsers) {
    $usersMeta = DB::connection('wp')->table('wp_users')
        ->join('wp_usermeta', 'wp_users.ID', '=', 'wp_usermeta.user_id')
        ->whereIn('wp_users.ID', $partOfUsers->pluck('ID'))
        ->get();

    $combinedUsers = $users->groupBy('ID');

    // Do your action for each user
}

This way the database load is not that much and you can do it in chunks.

And of course this will take time since the database is huge ;)

thebigk

8 years ago

Level 13

Super thanks, @bobbybouwmann ! I need to experiment with chunk sizes and get this working. :-)

Please or to participate in this conversation.