MySQL, Counting chain of continuous records based on a condition

In this post we’re talking about how to get the maximum count of continuous records that satisfy a predefined property.

The problem

Let's imagine that we want to make a query for a school that gets the student names with the maximum number of continuous semesters when the student got a GPA that’s less than 2 with the following conditions

• If the student's GPA for the semester is less than 2, count it in.
• if it’s a summer term with a GPA < 2, it’s not counted.
• if it’s a summer term with GPA > 2 it breaks the chain and counting starts over for that student.

Here’s an example

Let's consider that semesters are numbered as 1, 2 and 3, such that 3 is for the summer one.

If we have a table structure like that

student	year	semester	gpa
John	2012	1	1.2
John	2012	2	1.5
John	2012	3	1.0
John	2013	1	2.0

John’s count here is 2.

explanation : The first two semesters of 2012 are with GPA that's less than 2, so they’re counted in, summer term is not counted as per the second condition above and the first semester of 2013 is not counted because it’s GPA >= 2.0 and we are interested only in GPAs that’s < 2.

The solution

For something with that kind of complicated conditions, I thought it would be reasonable to use MySQL variables, don’t be scared, I promise they are way easier and natural than you first thought, let’s review what we want to do with them.

• The variable needs to increment whenever a non-summer semester with a GPA < 2.0 is encountered.
• The variable needs to reset whenever a semester of GPA >= 2.0 is encountered or the student has changed.

So, let’s start with the query

 SET @counter=0;
SET @student="";
SELECT student, max(semesterCount) as maxSemesterCount FROM (
 SELECT student,
   ( @counter:=IF(@student!=student, 0, @counter) ) AS counterReset,
   ( @student:=IF(@student!=student, student, @student) ) AS studentChange,
   ( @counter:=IF(gpa<2 AND semester!=3, @counter+1,
                 IF(gpa<2 AND semester!=3, @counter,
                 0)
               )
   ) AS semesterCount
 FROM (
   SELECT * FROM student_gpas
      ORDER BY student, educationalYear, semester
 ) q2
) q1
GROUP BY student;

Okay, let's break things down below and it’ll be a lot clearer, let’s examine the query line by line.

• We start by initializing our two variables to some initial values, these are the variables that will be used to track things down inside the query.
• Next we select the students and the maximum streak of semesters.
• Inside the braces of the FROM clase is where all the magic happens (I know, there isn’t any, but yeah), we start off with selecting the student, then check if the student has changed since last row (think of the query as a loop), if yes then the counter variable is reset, otherwise it’s not changed (conditions that change it based on the GPA are coming next, we’re just separating things here for more clarity).
• Next, same check for the student, if changed since last record, we assign the new value to the student variable
• Check if it’s a GPA less than 2.0 with a non-summer term, if so, increment the counter variable, otherwise no change
• Else if it’s a GPA less than 2.0 but a summer term, no change for the variable
• Else it’s definitely a GPA  that’s more than 2.0, so reset the counter variable.

That would give something like this

student	maxSemesterCount
John	2
Sarah	5
Christopher	3

A couple of things to note here

• This query can very much be optimized from many aspects, that’s the barebones version, the least that you can do is to reduce the number of records that’s being counted inside the query.
• Notice that we didn’t get the values directly from the student_gpas table, the reason for that is that we want the semesters to be consecutive to be able to count them properly, otherwise numbers would differ with each run.
• Views in MySQL don’t accept subqueries or variables, so if you want to use this query in a report designer or run from code there’s a silly workaround that works, create a table with that structure (student, maxSemesterCount fields) and insert these values in it at the time you open the report and make the report get its data from that table, I’m going to think of a better solution or if one of you guys may have been in the same situation and got to a real solution, please let me know, but for now, this works with a performance hit indeed.

That's it thank you for reading I hope that helps.

Laravel centralized project, multiple entry point directories

Note: This post depends on an htaccess rule and tested only on apache server, I don't know if that applies to other web servers as well

Note 2: This solves routing but still not assets, will update this post when I figure that out 

Long version
I work for a company that develops a web-based ERP system with Laravel as the chosen framework for that matter.
We decided to move to the cloud and start selling online so that meant we should be looking carefully for ways to preserve space and ease future maintenance and updates.

We've decided that we wanted a centralized project-different databases approach, but we also wanted every customer to have their own logo and favicon and specific settings such as language as well as their uploaded files, these kind of things needed to be in their own directories; so, one directory with all the meat, other small directories named after their owners pointing to the centralized project and accessed with a /directoryname .

Short version
What we are trying to do here is having a central directory with all the application logic and some other directories pointing to it containing instance-specific settings.

"Instance" refers to the directories that would point to the central app.

every instance would contain "index.php" and ".htaccess", and any other instance-specific files.

Steps

• First step was to change the index.php contents in other -instance- directories as that is the entry point for the app when "/directory" is typed as the URL in the browser.

paths to "autoload.php" and "start.php" were changed to point to the central directory, and instance-specific variables such as instance name and its database name were loaded from a file in the instance directory itself.

That loaded the required classes to run the application from the right places but there's still a problem where routes are trying to search for controllers in their directories.

• Next step is redirecting routes to the central app, in the htaccess file in the instance directories add the following rule 

RewriteRule ^/?${user.dir}/([a-z/.]+)$ central/$1 [R=301,L]

A little clarification:  ${user.dir} is a variable pointing to the current directory that's holding the htaccess file,
central is the name of your centralized app
Example: if your instance name is "dummy" and your centralize app is called "central", assuming a directory structure like this:

www
|-- central
|    |-- app 
|    |    |-- controllers
+-- dummy
 |-- index.php
 |-- .htaccess
 +-- settings.php

 All requests passing through dummy will be redirected to central, so that rule simply replaces the instance name with the central project name.

• Next is for database configuration, every instance had a settings file that held the database name as a constant, imported in index.php, then in the central app's config/database.php file the database name entry is taking the value stored in that constant.

So in short, these are the files that need to change: index.php, .htaccess in instance directories and database.php in the central project.

That's how you simulate multiple app instances with a centralized Laravel project.