2

Here is a working query

$connection = $this->_objectManager->create('\Magento\Framework\App\ResourceConnection');
      $conn = $connection->getConnection();
      $select = $conn->select()
          ->from(
              ['o' => 'sales_order']
          )
          ->where('o.created_at>=?', $place_date);
      $data = $conn->fetchAll($select);

Now i want to Left join with table sales_order_address

ON sales_order_address.parent_id = sales_order.entity_id

and want to select sales_order_address.street too.

3 Answers 3

5

The join Method should take the table as the first and the on statement as the second attribute. So something like this should work:

$connection = $this->_objectManager->create('\Magento\Framework\App\ResourceConnection');
      $conn = $connection->getConnection();
      $select = $conn->select()
          ->from(
              ['o' => 'sales_order']
          )
          ->join(
            ['soa' => 'sales_order_address'],
            'soa.parent_id=o.entity_id'
          )
          ->where('o.created_at>=?', $place_date);
      $data = $conn->fetchAll($select);
2
  • it works for me. Commented Mar 10, 2016 at 11:27
  • Usage of Object Manager is not advised.
    – Max
    Commented Aug 28, 2021 at 18:01
0

Whilst the other solutions are not wrong, they are not quite ideal in the context of the Magento2 Framework. For scalability and maintenance reasons you should always stick to the Magento2 way, even if it means that it bears some overhead in the beginning.

First of all, you should never make use of ObjectManager, instead, use Dependency Injection in your __construct().

private \Magento\Framework\App\ResourceConnection $connection;

public function __construct(
    \Magento\Framework\App\ResourceConnection $connection
){
    $this->connection = $connection;
}

Additionally, declare your property (see line above construct).

If you already have a construct in place, just add the Dependency in the last row (might not be possible if you're implementing an Interface that has restrictive declarations for constructors, in this case, consider a different approach where you can modify the constructor).

Now you can create a new function that does what you want. I am lending the solution of David Nerholten, as it was quite correct:

public function joinMyStuff(string $createdAt): array
{
    $conn = $this->connection->getConnection();
    $select = $conn->select()
        ->from(['o' => 'sales_order'])
        ->join(['soa' => 'sales_order_address'], 'soa.parent_id=o.entity_id')
        ->where('o.created_at >= ?', $createdAt);
    
    return $conn->fetchAll($select) ?? [];
}
0

I used Dependency Injection for \Magento\Framework\App\ResourceConnection
then :

public function __construct(
    <your other injections>,
    \Magento\Framework\App\ResourceConnection $resourceConnection
) { 
    // <... your other properties>;
    $this->resourceConnection = $resourceConnection;
}

// ...

public function doJoin(): array
{

    $conn = $this->resourceConnection->getConnection();
    $query = "SELECT column_names_comma_separated 
                    FROM `table_name` 
                    LEFT JOIN `join_table_name` ON table_name.unique_column = join_table_name.unique_column 
                    WHERE `condition_column_name` = ".$variable.";";

    return $conn->fetchAll($query) ?? [];
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.