Magento2 Join two tables for admin grid module creation from scratch

Hi, everybody! I had a chance to get my hands on the latest Magento 2 and work on a learning project. I decided to create a custom module that features an admin listing pulling data from two separate tables.

In many cases, we need to display listings from two tables by using MySQL Joins. This tutorial provides a step-by-step explanation of how to achieve this from scratch.

What You Will Learn

By the end of this tutorial, you will be able to:

• Create a module installable via Composer.
• Understand the controller and rewrite system.
• Work with blocks, layouts, and templates.
• Interact with the database using Models and Resource Models.
• Setup an admin listing with joins from two tables using UI Components.

Step 1: Module Registration

First, create your composer.json and registration.php to tell Magento about your module.

Ekvi/adminjoins/registration.php

<?php 
\Magento\Framework\Component\ComponentRegistrar::register( 
    \Magento\Framework\Component\ComponentRegistrar::MODULE, 
    'Ekvi_Adminjoins', 
    __DIR__ 
); 
?>

Step 2: Database Setup (InstallSchema)

We need to create two tables: ekvi_employee and ekvi_employee_salary. We will join these on salary_id.

// Ekvi/adminjoins/Setup/InstallSchema.php snippet
$table = $setup->getConnection()->newTable($setup->getTable('ekvi_employee'))
    ->addColumn('id', Table::TYPE_INTEGER, null, ['identity' => true, 'primary' => true], 'Employee Id')
    ->addColumn('name', Table::TYPE_TEXT, '200', [], 'Employee Name')
    ->addColumn('salary_id', Table::TYPE_SMALLINT, '2', [], 'Current Salary');
$setup->getConnection()->createTable($table);

Step 3: The Collection Join Logic

The "magic" happens in your Resource Model Collection. We override _initSelect() to perform the left join.

Ekvi/adminjoins/Model/ResourceModel/Employee/Collection.php

protected function _initSelect()
{
    parent::_initSelect();
    $this->getSelect()->joinLeft(
        ['secondTable' => $this->getTable('ekvi_employee_salary')],
        'main_table.salary_id = secondTable.salary_id',
        ['secondTable.salary_id as salaryId', 'salary']
    );
}

Step 4: UI Component Configuration

Magento 2 uses UI Components for admin grids. You need to define a data source in di.xml and configure the grid columns in adminjoins_employee_listing.xml.

By defining your data source to point to your custom collection, the admin grid will automatically handle the joined data, allowing you to display and filter by fields from both tables seamlessly.

This approach is powerful because it leverages Magento's native UI Component framework while giving you full control over the underlying SQL query.