S
S
Sergey2013-11-02 04:47:03
SQL
Sergey, 2013-11-02 04:47:03

What is the best way to write a SQL query?

I'm trying to make a nice, normally readable SQL query for a database like this:

Tables:
users [id, name] // no more fields needed for this example
users_events [id, timestamp, event_type, event_value, user_id]
users_data [id, timestamp, data_name, data_value , user_id]

Data is added to users_events after each user event. Example event: change of residence.
Looks like:

{ 
  id: 123, 
  timestamp: '10.03.13', 
  event_type: 'city',
  event_value: 'Moscow', 
  user_id: '888'
}


Users_data stores user parameters, for example - Username (and if it changes, the old record will remain, and the last one will be used and data from it will be displayed). Another example is Phone Numbers. There may be several of them, and all of them may be active.
{
  id:122,
  timestamp: , // некая дата
  data_name: 'last_name',
  data_value: 'Vasechkin', // сначала было старое имя
  user_id: 888
}
{
  id:123,
  timestamp: , // некая дата
  data_name: 'last_name',
  data_value: 'Pupkin', // потом заменили на новое
  user_id: 888
}
{
  id:124,
  timestamp: , // некая дата
  data_name: 'phone',
  data_value: '1234567',
  user_id: 888
}
{
  id:125,
  timestamp: , // некая дата
  data_name: 'phone',
  data_value: '1233214',
  user_id: 888
}



Actually, what kind of request is needed:
You need to select from the database of such users who have the last place of residence, for example, 'Moscow', as well as the active (last) name LIKE% pup% and, in addition, one of the numbers has the number 7

+ you need count the number of records in the database (for some reason my COUNT() broke on joins).

I must say right away that readability and finishability are quite important. It will be just great if the request fits into CodeIgnitter's database API

PS: It should be mentioned that reworking the database structure is not an option, since it is actively used and there is a lot of data that cannot be lost in any way.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question