0

I am using MSSQL as my database and Medoo isn't successfully performing select queries with an INNER JOIN.

This is my query.

$configurations = $this->database->select("application_configurations", [
  "[><]environments" => ["active_environment_id", "id"]
  ], [
    "environments.name"
  ], ["application_configurations.application_id" => $id]);

After looking at the query logs, this is what I found was being outputted:

'SELECT [environments].[name] FROM [application_configurations] INNER JOIN [environments] USING ([active_environment_id], [id]) WHERE [application_configurations].[application_id] = 1'

MSSQL doesn't like this :

""active_environment_id" is not a recognized table hints option."

I would have written this :

select environments.name from application_configurations
inner join environments on environments.id = application_configurations.active_environment_id
where application_configurations.application_id = 1

Very simple queries work

$configurations = $this->database->select("application_configurations",
  ["active_environment_id"],
  ["application_id" => $id]
);

How can I get this to work with MSSQL?

EDIT

I tried using the left join query as suggested in the comments.

It still did not work, this was the query done behind the scenes.

SELECT [environments].[name] FROM [application_configurations]
 LEFT JOIN [environments] USING ([active_environment_id], [id])
 WHERE [application_configurations].[application_id] = 1`
odgers.chris
  • 1
  • 1
  • 1
    `USING ([active_environment_id], [id])` what is it mean? It seems that it should be a condition to join instead. Like `ON application_configurations.active_environment_id = environments.id` – Nguyễn Văn Phong Jan 17 '20 at 12:48
  • The [Medoo documentation](https://medoo.in/api/select) makes it look like maybe `"[><]environments" => ["active_environment_id", "id"]` should instead be `"[>]environments" => ["active_environment_id" => "id"]`. – AlwaysLearning Jan 17 '20 at 13:20
  • That is what I thought, the sql statement is what Medoo is printing out behind the scenes – odgers.chris Jan 17 '20 at 13:20

1 Answers1

0

It does work, you have just misunderstood the instructions for joins (which is pretty standard considering how brief the documentation on them is).

What you want is the following (for an inner join):

$database->select('application_configurations',
    [       //  join
        "[><]environments" => ["active_environment_id" => "id"],
    ], [    //  columns
        'environments.name'
    ], [    //  where
        'application_configurations.application_id' => $id
    ]
);

If that is also a view that your application is accessing often, you may want to consider making a view of it on your server. Calling a view on your server will be cheaper that a query any day.

I hope that is just what you need.

PinothyJ
  • 54
  • 3