Magento 2 - How can I write query in Magento

Question

I have a SQL query that joins two tables.

SELECT ot.name, org.name from ayakil_organization as org INNER JOIN ayakil_organization_type as ot ON org.type_id = ot.type_id ORDER BY ot.name ASC

Here ayakil_organization, ayakil_organization_type are two tables.

my resolver class is like below.

$collection = $this->_organizationFactory->create()->getCollection();
$collection->getCollection()->getSelect()
            ->join(
                ['ot' => 'e25_organization_type'],
                'main_table.type_id = ot.type_id',
                array("*")
            )
            ->setOrder('ot.name','ASC');

If i use the collection query like this, i am getting an error.Can anyone help me?

Answer 1

You can try this..

$this->ayakilOrganizationModel
    ->getCollection()
    ->getSelect()
    ->join(
        ['ot' => 'ayakil_organization_type'],
        'main_table.type_id = ot.type_id',
        array("*")
    )
    ->setOrder('ot.name','ASC');

This will return same query which you want.

Hope this will help you!