drupal 7 join two tables

Question

How can I join two tables together in drupal 7.

I have a simple database schema and I want to join it to the user table could someone give me a coded example of joining a custom build table to the user table.

If I take a basic table example

$schema['location'] = array (

'descritiption' => 'location',

'fields' => array (
    'locationID' =>  array(
        'description' => 'primary key for patients table',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE),
 ),

'addressone' => array (
 'description'    => 'this shows the first line of the address',
    'type'         => 'TEXT'
),
'county' => array (
    'description' => 'this is a county list'

),
'postcode' => array (
  'deescription' => 'stores the postcode'  
),

    'primary_key' => array('locationID')



    );

How would I join this to the user table. I would like to query data from both tables. So when I add a record into the user table I want the custom table to have the data added as well so there is a link between the two tables.

here is a high level example

user table data

uid 1

username alex

visitor

vis id 1

firstname Alexander

lastname Brown

So what i want to happen is when i add data into the user table I build a relationship to what is entered in the visitors table. I know you can use a join which has kindly been explained to me but I am not sure what is meant by building the relationships in code.

Answer 1

JOINs are a query-level concept and don't really make a lot sense in the context of a schema definition.

I suspect what you're asking is how to add a foreign key from your location table to the users table. If so I'm afraid you're out of luck; Drupal does not enforce foreign key relationships, so you'll need to define and maintain your relationships at a code level.

There is a foreign keys property that can be applied to tables defined by the schema API, but it is for documentation purposes only, no foreign keys are ever added at the database level.

Just in case you are asking how to join a table in a query:

$query = db_select('location', 'l')
  ->fields('l');

$query->join('users', 'u', 'u.uid = l.uid_column_name');

$results = $query->execute();