employee_system's Introduction

README - Employee System Assignment

Technologies used

Ruby
Javascript
HTML
CSS
Rails
PostgreSQL
Bootstrap

Approach

As the initial instructions stated a CRUD application, I chose Ruby on Rails as it would ease the process of dealing with all CRUD actions.
This left me more time to deal with the main issue which was accessing the hiearchy of each employee in the SHOW route.
I created an ERD of what a table would look like deciding finally to go with a single employee table that has an ID, a name, and a manager_id which would be referencing the employee's ID.(Shown Below)

Recursive Query

The biggest issue was dealing with the recursive query that would handle fetching the employee's manager and every subsequent manager in the Hiearchy. The query is as follows:

 @hierarchy = Employee.find_by_sql("
      WITH RECURSIVE EmployeeCTE AS (
          
          --ANCHOR--
        SELECT id, name, manager_id FROM employees 
        where id = #{params[:id]}
        UNION 

          --RECURSION--
        SELECT employees.id, employees.name, employees.manager_id
        FROM employees JOIN EmployeeCTE
        On employees.id = EmployeeCTE.manager_id
      )
        SELECT * from EmployeeCTE
     
    ")

In this query, The anchor first gets executed passing in the ID params from the SHOW route which returns the employee ID, name and manager_id result set.
This result set is now the input for the recursion and returns the original Employee's manager name, id and manager_id
This runs until a result set where manager_id is null is returned.
The final line is SELECT * from EmployeeCTE to return the id, name and manager_id for all rows.
Once this was solved I added bootstrap to add some stylings to the application.