1
00:00:06,600 --> 00:00:09,690
- Imagine we want to select
employees from a table.

2
00:00:09,690 --> 00:00:11,880
You'd write a select statement like this.

3
00:00:11,880 --> 00:00:14,850
I'm gonna select the employee
id, the name, the salary,

4
00:00:14,850 --> 00:00:17,760
and the region from the
employees_temp table.

5
00:00:17,760 --> 00:00:18,900
Straightforward enough.

6
00:00:18,900 --> 00:00:21,300
Obviously you could do
something much more complicated,

7
00:00:21,300 --> 00:00:23,490
but that's a good starting point.

8
00:00:23,490 --> 00:00:26,490
Right, so we have a very handy function.

9
00:00:26,490 --> 00:00:27,900
We've already seen this.

10
00:00:27,900 --> 00:00:29,730
In the PooledConn structure,

11
00:00:29,730 --> 00:00:32,670
there's a handy method called query_map.

12
00:00:32,670 --> 00:00:34,020
When you call query_map,

13
00:00:34,020 --> 00:00:38,040
you pass in a select statement,
SELECT * from employees,

14
00:00:38,040 --> 00:00:40,440
and it'll come back with a result set.

15
00:00:40,440 --> 00:00:42,900
You've also passed in a closure.

16
00:00:42,900 --> 00:00:44,850
Closure, like a map function,

17
00:00:44,850 --> 00:00:47,520
and it'll say for each
row in the result set,

18
00:00:47,520 --> 00:00:50,700
this is how I'd like you to
convert it into something else.

19
00:00:50,700 --> 00:00:53,280
So instead of getting
back just a group of rows,

20
00:00:53,280 --> 00:00:55,350
you get back a group of objects

21
00:00:55,350 --> 00:00:58,500
which have been constructed
from those rows.

22
00:00:58,500 --> 00:01:00,240
Okay, so that's really handy.

23
00:01:00,240 --> 00:01:02,760
So let's see what the code
looks like in our demo.

24
00:01:02,760 --> 00:01:05,190
We can look at the main function

25
00:01:05,190 --> 00:01:08,340
and then we'll have a
look at select_employees.

26
00:01:08,340 --> 00:01:09,630
So here's my code.

27
00:01:09,630 --> 00:01:11,760
I've reset the main function,

28
00:01:11,760 --> 00:01:13,890
the main code to be
exactly the same as before.

29
00:01:13,890 --> 00:01:17,190
So in main, basically
everything is still uncommented.

30
00:01:17,190 --> 00:01:19,440
And in fact, it can remain uncommented

31
00:01:19,440 --> 00:01:22,800
because this is the last
demo that we're gonna run.

32
00:01:22,800 --> 00:01:25,080
We've seen how to get a connection

33
00:01:25,080 --> 00:01:28,110
and how to create a temporary table.

34
00:01:28,110 --> 00:01:29,700
We've seen how to insert employees

35
00:01:29,700 --> 00:01:32,460
using that handy prams macro.

36
00:01:32,460 --> 00:01:35,430
And now we're gonna see
how to select employees

37
00:01:35,430 --> 00:01:36,360
on that connection.

38
00:01:36,360 --> 00:01:39,000
And that function is
gonna return back a vector

39
00:01:39,000 --> 00:01:41,370
of fully fledged employee structures.

40
00:01:41,370 --> 00:01:45,180
Remember the employee type,
an employee has a name

41
00:01:45,180 --> 00:01:47,220
and an ID and a salary and region.

42
00:01:47,220 --> 00:01:49,500
It's gonna construct a function basically

43
00:01:49,500 --> 00:01:51,720
which will say give me an id

44
00:01:51,720 --> 00:01:53,580
and a name and a salary and a region

45
00:01:53,580 --> 00:01:57,840
and I will give you back an
employee containing that data.

46
00:01:57,840 --> 00:01:59,700
Okay, so bearing that in mind,

47
00:01:59,700 --> 00:02:01,470
let's go back into our main code

48
00:02:01,470 --> 00:02:03,330
and let's have a look at this function.

49
00:02:03,330 --> 00:02:05,640
It'll return back a vector of employees,

50
00:02:05,640 --> 00:02:07,470
which we then output.

51
00:02:07,470 --> 00:02:09,960
It iterates with the vector of employees

52
00:02:09,960 --> 00:02:12,420
and outputs each employee.

53
00:02:12,420 --> 00:02:14,520
Remember that the employee structure

54
00:02:14,520 --> 00:02:16,500
supports default formatting.

55
00:02:16,500 --> 00:02:18,480
We use curly brackets like that.

56
00:02:18,480 --> 00:02:21,630
It basically looks on your employee type.

57
00:02:21,630 --> 00:02:25,260
It looks for implementation
of the display trade

58
00:02:25,260 --> 00:02:27,090
and we have it.

59
00:02:27,090 --> 00:02:30,210
Our employee does indeed implement display

60
00:02:30,210 --> 00:02:32,520
and it outputs just the
details of the employee,

61
00:02:32,520 --> 00:02:34,920
the id, the name, the
salary, and the region.

62
00:02:34,920 --> 00:02:36,720
So we're looking in a good shape here.

63
00:02:36,720 --> 00:02:37,920
Let's just have a look at the code

64
00:02:37,920 --> 00:02:39,843
to actually do the select.

65
00:02:43,710 --> 00:02:46,143
Right, it's very elegant.

66
00:02:47,220 --> 00:02:51,780
So SELECT form the employees table,

67
00:02:51,780 --> 00:02:53,670
select the employee_id

68
00:02:53,670 --> 00:02:56,400
and the name and the
salary and the region.

69
00:02:56,400 --> 00:03:00,840
Okay, so we've got four
employees in our database table.

70
00:03:00,840 --> 00:03:05,840
So for each row that comes
back in the result set,

71
00:03:06,150 --> 00:03:08,830
it'll automatically invoke this closure

72
00:03:09,990 --> 00:03:12,360
with the columns for that row.

73
00:03:12,360 --> 00:03:14,880
Okay, so the first row, that was me, Andy,

74
00:03:14,880 --> 00:03:19,880
id one, salary 25K, and a region of Wales.

75
00:03:21,060 --> 00:03:26,060
So this here is a tuple
that contains the id,

76
00:03:26,850 --> 00:03:29,340
the name, the salary, and the region.

77
00:03:29,340 --> 00:03:32,670
And all I need to do is basically create

78
00:03:32,670 --> 00:03:35,730
from that data a new employee,

79
00:03:35,730 --> 00:03:37,860
okay, using the employee new function,

80
00:03:37,860 --> 00:03:39,540
that will create an employee object

81
00:03:39,540 --> 00:03:42,090
for the first row in the result set.

82
00:03:42,090 --> 00:03:43,590
And then for the second row,

83
00:03:43,590 --> 00:03:45,540
it'll call this closure again

84
00:03:45,540 --> 00:03:48,600
to map that row into an employee object

85
00:03:48,600 --> 00:03:52,890
and it collects them together
into a vector of employees

86
00:03:52,890 --> 00:03:54,960
and that's what gets returned back.

87
00:03:54,960 --> 00:03:57,630
Back in main, the vector of employees

88
00:03:57,630 --> 00:03:59,310
that comes back from that function

89
00:03:59,310 --> 00:04:01,593
are then displayed on the screen.

90
00:04:02,580 --> 00:04:05,440
So all being well, this should be

91
00:04:07,262 --> 00:04:11,023
the high point of the demo if it works.

92
00:04:16,170 --> 00:04:18,453
So let's do a cargo run.

93
00:04:24,810 --> 00:04:26,640
And it has worked.

94
00:04:26,640 --> 00:04:27,810
I knew it was gonna work, to be honest.

95
00:04:27,810 --> 00:04:29,910
I'd already tried it beforehand.

96
00:04:29,910 --> 00:04:32,610
So it had from the top,

97
00:04:32,610 --> 00:04:36,600
it had got a connection to
my containerized database,

98
00:04:36,600 --> 00:04:39,393
created a temporary table employees_temp,

99
00:04:40,260 --> 00:04:43,740
inserted four rows into the table,

100
00:04:43,740 --> 00:04:46,320
and then selected the rows back again,

101
00:04:46,320 --> 00:04:49,110
give me back a bunch of model objects,

102
00:04:49,110 --> 00:04:52,980
employees, which I then
display on the screen.

103
00:04:52,980 --> 00:04:57,980
And if it didn't already, that is me,

104
00:04:59,070 --> 00:05:02,340
my wife, my daughter, and my son.

105
00:05:02,340 --> 00:05:03,603
A nice way to finish off.